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 all,
Below is my sample data. I create two measures to sum 2018 data and 2019 data. However, I have some complex KPI, for example, rolling 3 month KPI.
Rolling 3 month calculation:
2018 Jan= null (becoz no 2017 Dec and Nov data)
2018 Feb = null (becoz no 2017 Dec data)
2018 Mar= 2018Jan+2018Feb+2018Mar
....
2019 Dec=2019 Oct+2019 Nov+2019 Dec
date | Month | amount | Order |
1/1/2018 | Jan | 1 | 1 |
2/1/2018 | Feb | 2 | 2 |
3/1/2018 | Mar | 3 | 3 |
4/1/2018 | Apr | 4 | 4 |
5/1/2018 | May | 5 | 5 |
6/1/2018 | Jun | 6 | 6 |
7/1/2018 | Jul | 7 | 7 |
8/1/2018 | Aug | 8 | 8 |
9/1/2018 | Sep | 9 | 9 |
10/1/2018 | Oct | 10 | 10 |
11/1/2018 | Nov | 11 | 11 |
12/1/2018 | Dec | 12 | 12 |
1/1/2019 | Jan | 13 | 1 |
2/1/2019 | Feb | 14 | 2 |
3/1/2019 | Mar | 15 | 3 |
4/1/2019 | Apr | 16 | 4 |
5/1/2019 | May | 17 | 5 |
6/1/2019 | Jun | 18 | 6 |
7/1/2019 | Jul | 19 | 7 |
8/1/2019 | Aug | 20 | 8 |
9/1/2019 | Sep | 21 | 9 |
10/1/2019 | Oct | 22 | 10 |
11/1/2019 | Nov | 23 | 11 |
12/1/2019 | Dec | 24 | 12 |
Now I want to create a chart like below. Is it possible to do that?
Thanks a lot.
Proud to be a Super User!
Solved! Go to Solution.
I worked this out. Acutally, you provided me a very useful idea which is pull the year to legend. I tried this on my end. It works. Thanks for your help on this.
Proud to be a Super User!
Hi Ryan,
I've created 2 measures for "start of the period" and "end of the period" for your rolling 3 months. After that another measure that calculates the rolling sum. If you have the rolling sum, then you just need to create a visualisation where you use a year as legend. And one more thing: You have to sort the column Month by Month Order to be able to display Jan, Feb and so on in the expected order and not alphabetically.
Period Start = FIRSTDATE(DATESINPERIOD(TestTable[date]; [Period End]; -3; MONTH))
Period End = LASTDATE(TestTable[date])
Rolling Sum = CALCULATE( SUM(TestTable[amount]); DATESBETWEEN(TestTable[date]; [Period Start]; [Period End]) )
I worked this out. Acutally, you provided me a very useful idea which is pull the year to legend. I tried this on my end. It works. Thanks for your help on this.
Proud to be a Super User!
Thanks for your help. However, I think your chart is not correct. Based on my logic, we should not have value for 2018 Jan and Feb.
And for example, the value of 2018 May is 5 in your chart. That is only the summary of one month. I think the correct value should be 5+4+3= 12.
Any thoughts on this?
Thanks in advance.
Proud to be a Super User!
Hi @ryan_mayu,
I'm sorry I've overseen that you want to remove results when the whole interval isn't there. Maybe there is a more elegant solution for that, but I used a check at the end if the length of the period is the expected length.
And I've also corrected the error you found with May 2018. It worked as a table but after that I converted in a visualisation and didn't checked the value again (the fix was to take all rows in Calculate again).
Rolling Sum = VAR PeriodLength = 3 VAR PeriodEnd = LASTDATE ( TestTable[date] ) VAR PeriodStart = FIRSTDATE ( DATESINPERIOD ( TestTable[date]; PeriodEnd; - PeriodLength; MONTH ) ) VAR IsPeriodThreeMonths = DATEDIFF ( PeriodStart; PeriodEnd; MONTH ) = PeriodLength - 1 VAR CalculateRollingSum = CALCULATE ( SUM ( TestTable[amount] ); ALL ( TestTable ); DATESBETWEEN ( TestTable[date]; PeriodStart; PeriodEnd ) ) VAR Result = IF ( IsPeriodThreeMonths; CalculateRollingSum; BLANK () ) RETURN Result
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 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |