Rolling 12 month issue

[rogerdea]

I have used DAX to do a rolling 12 month sum, but have a rogue value, which i think is where one of the months has no data value (see image, its month 2021-01).

How can i get around this so that it includes the blank in the 12M sum? (column B in the image)  I already tried added IF(ISBLANK to te DAX but it didn't seem to help.

My DAX is:

R12M Sum =

CALCULATE(

    SUM( Data[Amount] ),

    DATESINPERIOD(DateTable[Date], MAX(Data[DateTime] ), -12,  MONTH )

)

[rogerdea]

Thanks very much there were two things i needed to do to fix the DAX.

1)  Mark date table as the date table.

2) Change the second date in my DAX to be the date field from the date table, NOT my fact table.

[amitchandak · Accepted solution]

@rogerdea , Make sure to join with Data and Date table in 1-M single direction, The date in the data table should not have a timestamp other than 12 AM

You should date from date table in visual

explained the reasons here Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI:https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

Rolling Months Formula: https://youtu.be/GS5O4G81fww