Forum Discussion

KatkaS's avatar
KatkaS
Icon for Post Patron rankPost Patron
3 years ago

12M rolling with filter

Hi, could somebody please help me how to create 12M rolling measure with FILTER?   I'm trying to create 12M rolling measures, I used two with the same results, they seem to be ok (1st mesure and 2...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

KatkaS , You should use a date table joined with date of your table in such cases

 

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

or

 

Rolling 12 = CALCULATE([Net], WINDOW(-11,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

 

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

 

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f