Cannot use Date column in IF DAX query

[JAB]

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 in november 2020 there is not data for going 12 month back.

The formual is like this:

PPM Produced R12 M:=
var _pp=
CALCULATE (
[PPM Produced],

DATESINPERIOD (
'Calendar'[CalendarDate],
MAX ( 'Calendar'[CalendarDate] ),
-1,
YEAR
)
)

return
if('Calendar'[CalendarDate]>20201130,_pp,0)

Any ideas on how I can solve the issue

[v-kelly-msft · Accepted solution]

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]

It worked, thank you very much 🙂

[PC2790]

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]

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.