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,
I'm fairly new to Power BI and I am having difficulty creating a rolling 12m sum measure to be used in tables and charts. I have created a mock up of the table, I am aiming for in excel for illustration:
The SQL data table I am using has the structure PeriodName | CompanyName | AccountDescription | Value. I have added columns so that the resultant table has the structure PeriodName | PeriodID | PeriodDate| CompanyName | AccountDescription | Value | IncludeFlag
For further information,
Any help would be greatly appreciated, since I can't seem to generate a calculated measure which gives me exactly what I'm looking for!!
Solved! Go to Solution.
I created a test Table1 like below. You can use this measure to get what you want.
Measure = IF ( FIRSTDATE ( Table1[PeriodDate] ) >= DATEADD ( FIRSTDATE ( ALL ( Table1[PeriodDate] ) ), 11, MONTH ), CALCULATE ( SUM ( Table1[Value] ), DATESINPERIOD ( Table1[PeriodDate], FIRSTDATE ( Table1[PeriodDate] ), -12, MONTH ), ALLEXCEPT ( Table1, Table1[PeriodDate], Table1[CompanyName] ), Table1[IncludeFlag] = 1 ), "N/A" )
If you want a filter to select the companies, you can add the CompanyName in a slicer.
“N/A” or other word is recommended. Because blank is ambiguous.
Best Regards,
Herbert
I created a test Table1 like below. You can use this measure to get what you want.
Measure = IF ( FIRSTDATE ( Table1[PeriodDate] ) >= DATEADD ( FIRSTDATE ( ALL ( Table1[PeriodDate] ) ), 11, MONTH ), CALCULATE ( SUM ( Table1[Value] ), DATESINPERIOD ( Table1[PeriodDate], FIRSTDATE ( Table1[PeriodDate] ), -12, MONTH ), ALLEXCEPT ( Table1, Table1[PeriodDate], Table1[CompanyName] ), Table1[IncludeFlag] = 1 ), "N/A" )
If you want a filter to select the companies, you can add the CompanyName in a slicer.
“N/A” or other word is recommended. Because blank is ambiguous.
Best Regards,
Herbert
Thanks for the help! That's worked perfectly!
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |