Forum Discussion
Helper IVRolling 12 month issue
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:
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-bd52912a5bd4Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))
Rolling Months Formula: https://youtu.be/GS5O4G81fww
2 Replies
- amitchandak
Super User
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-bd52912a5bd4Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))
Rolling Months Formula: https://youtu.be/GS5O4G81fww
rogerdeaThanks 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.