Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 2nd measure)
Problem starts, when I try to add FILTER on a specific area that I have in the data (2nd measure with problem).
AREA | Value | Period |
Finance | 1000 | 01/01/2023 |
IT | 2000 | 01/01/2023 |
Sales | 3000 | 01/01/2023 |
Finance | 1200 | 01/02/2023 |
IT | 2500 | 01/02/2023 |
1st measure:
Hello, could anyone check the filter question in my reply on 08-09 please? Thank you!!
@amitchandak Thank you very much, Amit, for your prompt reply!
I tried your measure (with relationship between period table and period of the data) and and it worked well, but when I added there FILTER on area, it showed only the current period..
Measure with filter:
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |