Modify DAX fromula PREVIOUSMONTH

[Higgs11]

Hello all,

I have a report that feeds off current month results. All of the other columns in the table ( CM results vs Budget, CM vs Prior Month, etc) . I would like to add 3 columns , 1 each for the prior 3 months. The formula below works perfect for the priormonth, but I need another to go back 2 months, and 3 months. Any suggestions?

ACT LastMonth =

CALCULATE (

    [Actuals.],

    PREVIOUSMONTH( '3_DateDim'[Date] )

)

The '3_DateDim'[Date]) in the table defaults to the current month, which is a Y/N filter.

Please note I use a date table common with what users suggest and built relationships.

[hnguy71 · Accepted solution]

You can use DATEADD

An example would be:

PreviousMonth = CALCULATE(SUM([Actuals]), DATEADD(DateTable[Date], -3, MONTH)

[Higgs11]

This worked

I removed the SUM Dax expression since the field was a measure. I created 3 of these and put them in my results table. whats nice is that when the current month changes, the prior 3 months change automatically as well.

Prior 3rd Month =

CALCULATE ( [Actuals],

    DATEADD ( 'DateDim'[Date], -3, MONTH )

)

Now, only if the column name can be dynamic as well to match the months name. I will research these awesome blogs to see if that's possible.

Thank you for the help 🙂

[hnguy71]

I'm glad it worked out for you. Open a new thread with the issue and let's see what we can do about that. My first thought is to use Power Query to do that