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!

v-kelly-msft's avatar
v-kelly-msft
Community Support
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!

JAB's avatar
JAB
Advocate II
5 years ago

It worked, thank you very much 🙂