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 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

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!

4 Replies

  • 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.

  • 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 🙂