Creating Measure in Date Table for Current Month, Previous Month, etc.

[CSMcVey]

I have a date table I am importing into a PowerPivot workbook. This data table contains a column entitled, 'date' which has a date for every day from 2000 to 2020. It has other basic columns like month name, month number, year, day, business day flag, etc.

I would like to create a measure which looks @ another measure called 'LastDate' (this identifies the last.max date in my data set) and then sets the measure to one of 5 values:

1. Current Month
2. Current Month -1 (aka Previous Month)
3. Current Month -2
4. Current Month -3
5. Current Month -4+ (the rest)

For example if the LastDate was equal to 3/13/13:

1. all dates in my date table where the Month is March and the Year is 2013 would have this new measure be equal to 'Current Month'
2. all dates in my date table where the Month is Feb and the Year is 2013 would have this new measure be equal to 'Current Month -1'
3. all dates in my date table where the Month is Jan and the Year is 2013 would have this new measure be equal to 'Current Month -2'
4. all dates in my date table where the Month is Dec and the Year is 2012 would have this new measure be equal to 'Current Month -3'
5. all dates in my date table where the date is earlier than Dec 2012 would have this new measure be equal to 'Current Month -4+'

The part I am struggling with are the months that involve a year change.

Thanks in advance

 

[Jacob Barnett]

The answer is here: The Greatest Formula in the World, Part 3 « PowerPivotPro

Rob's book which is a laughable $10 for the colour PDF version (DAX Formula, Power Pivot, PowerPivot, DAX, Excel, Excel 2010, Excel 2013, Microsoft Excel, Microsoft Power Pivot) does an even better job of explaining the technique.

Post back if you are still struggling.

Jacob

 

[gand3rson]

Gfiw fw!!!1