Forum Discussion

JAB's avatar
JAB
Advocate II
5 years ago
Solved

Cannot use Date column in IF DAX query

I have a rolling 12 month calculation which should only show a value from 20201130 and onwards, so the december value for 2020 will be based on the calculation going 12 month back. The reason is that...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi JAB ,

     

    If you need a calculated column,using below dax expression:

    _PPM Produced R12 M = 
var _pp= CALCULATE([Measure],DATESINPERIOD('Table'[Date],'Table'[Date],-1,YEAR))
Return
IF('Table'[Date]>DATE(2020,11,30),_pp,0)

    If a measure is needed,then try:

    PPM Produced R12 M = 
var _pp= CALCULATE([Measure],DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date]),-1,YEAR))
Return
IF(MAX('Calendar'[Date])>DATE(2020,11,30),_pp,0)

    And you will see:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

PC2790's avatar
PC2790
Community Champion
5 years ago

Can you try using this formula instead of your DATESINPERIOD calculation:

CALCULATE([PPM Produced],DATESBETWEEN(DatesTable[Date],MAX(Calendar[Calendardate])-365,MAX(Calendar[Calendardate])))
JAB's avatar
JAB
Advocate II
5 years ago

Thank you for your input. I am not certain how it shold be used, the calculation of the variable works fine, it removing those values/calculations older than 20201130 that is the issue.

I did try your suggestion but without luck.