Forum Discussion

Higgs11's avatar
Higgs11
Frequent Visitor
7 years ago
Solved

Modify DAX fromula PREVIOUSMONTH

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.
Need Help
  • hnguy71's avatar
    hnguy71
    7 years ago

    You can use DATEADD

     

    An example would be:

     

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

3 Replies

  • hnguy71's avatar
    hnguy71
    Super User
    7 years ago

    You can use DATEADD

     

    An example would be:

     

    PreviousMonth = CALCULATE(SUM([Actuals]), DATEADD(DateTable[Date], -3, MONTH)
    • Higgs11's avatar
      Higgs11
      Frequent Visitor
      7 years ago

      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's avatar
        hnguy71
        Super User
        7 years ago

        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