Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
I've found a ton of articles and other posts on this, but they all either require using dates, or don't work for averaging an aggregation like SUM([Value]).
Basically, I need to graph a rolling average of a sum, without using dates. I've put some sample data at the bottom of this post, if that helps.
I tried making a Quick Measure with a "fake" date using the Fiscal Year and Period and a placeholder day (1).
Fiscal Yr/Pd as Date = DATE( Bookings[Fiscal Year], RIGHT(Bookings[Fiscal Period],2), 1)
The Quick Measures almost work, but the axis is stuck in "Year MonthName" format that does not work for me ("2022 P4" is being shown as "2022 April") which is incorrect, and I can't seem to change the formatting. Changing the axis to anything else breaks the Quick Measure.
Is there any way I can get to something like this using DAX?
Please let me know if there's anything else I can post to help, like more sample data or the DAX of the quick measure!
Thank you so much for any help, I really appreciate it. I'm coming from years of using Tableau and am still learning Power BI, and this is the first time I've gotten really stuck on something.
This sample data is a similar format to what I'm using:
Transaction ID | Fiscal Year | Fiscal Period | Value | Industry |
3415 | 2021 | P01 | 94 | a |
9762 | 2021 | P01 | 7 | b |
9287 | 2021 | P01 | 83 | c |
2803 | 2021 | P02 | 96 | d |
1378 | 2021 | P02 | 91 | a |
5135 | 2021 | P02 | 100 | c |
1713 | 2021 | P03 | 68 | a |
4715 | 2021 | P03 | 88 | b |
7410 | 2021 | P04 | 92 | c |
4108 | 2021 | P04 | 73 | d |
7537 | 2021 | P04 | 45 | a |
5592 | 2021 | P05 | 41 | c |
7978 | 2021 | P05 | 1 | a |
6173 | 2021 | P06 | 8 | b |
6998 | 2021 | P06 | 21 | c |
1812 | 2021 | P06 | 49 | d |
2695 | 2021 | P06 | 97 | a |
7268 | 2021 | P07 | 69 | c |
7550 | 2021 | P07 | 11 | a |
8560 | 2021 | P08 | 80 | b |
9633 | 2021 | P08 | 19 | c |
6884 | 2021 | P08 | 81 | d |
9440 | 2021 | P09 | 36 | a |
8875 | 2021 | P09 | 42 | c |
5254 | 2021 | P10 | 58 | a |
5999 | 2021 | P10 | 49 | b |
3176 | 2021 | P10 | 37 | c |
1176 | 2021 | P11 | 32 | d |
3022 | 2021 | P11 | 83 | a |
2403 | 2021 | P12 | 52 | c |
2795 | 2021 | P12 | 31 | a |
3740 | 2022 | P12 | 84 | b |
5317 | 2022 | P12 | 0 | c |
6017 | 2022 | P01 | 99 | d |
1947 | 2022 | P01 | 78 | a |
7923 | 2022 | P01 | 30 | c |
5024 | 2022 | P02 | 76 | a |
8578 | 2022 | P02 | 54 | b |
3733 | 2022 | P02 | 45 | c |
2005 | 2022 | P03 | 27 | d |
5305 | 2022 | P03 | 31 | a |
9027 | 2022 | P03 | 74 | c |
Hi @castewart ,
Please check whether the below screenshot is what you want. You can create a line chart, the fields setting as below: Axis: Fiscal Year and Fiscal Period Values: measures or aggration of fields.
In addition, you can refer the following links to get the rolling average values.
Rolling 12 Months Average in DAX
Calculate a Rolling Average in Power BI Using DAX
Best Regards
Hi yingyinr, thanks for your reply.
Yes, your screenshot is on the right path. The links you provided require a date, but I tried from this Calculate a Rolling Average in Power BI Using DAX:
Fiscal Yr/Pd as Date = DATE( Bookings[Fiscal Year], RIGHT(Bookings[Fiscal Period],2), 1)
3Pd RollingAvg =
VAR NumPds = 3
VAR RollingSum =
CALCULATE(SUM(Bookings[Bookings Fixed USD]),
DATESINPERIOD(Bookings[Fiscal Year/Pd as Date],LASTDATE(Bookings[Fiscal Year/Pd as Date]),-NumPds,MONTH)
)
RETURN
RollingSum/NumPds
I thought it would work better because it has SUM(Measure), and the other article (Rolling 12 Months Average in DAX) doesn't aggregate before averaging (if I'm reading it correctly? I'm not confident - I don't understand the VALUES part of the calc).
Anyway, I'm not getting any errors, but it's not working correctly 😞
I don't think the rolling sum is working properly. So many examples are just rolling averages, not rolling averages of sums.
Hi @castewart ,
Could you please provide some sample data of tables and the final desired result? And please provide an example to illustrate the logic of the calculation and the correct calculation result. If possible, please provide a simplified pbix file. Please remove sensitive data before sharing this information. Thank you. Later we can create/update the pbix file which fulfill your requirement base on your provided info.
Best Regards
Hi yingyinr,
I'm sorry for the delay! I really appreciate your help.
I recreated some examples using sample data and made it available to download on onedrive here
My measure names are all using underscores (_Example_Measure_Name) so they're at the top and easy to find!
Here's how it's laid out:
First line graph
- Good: Used "quick measure" feature to create a 3moRollingAvg measure (which works!)
- Bad: The quick measure forces a "Year MonthName" format in the x-axis, which doesn't work for me (since my "month" is actually a "fiscal period")
Second line graph
- Good: allows me to use my "fiscal year & fiscal period" x-axis
- Bad: The 3moRollingAverage measure does not work, and just seems to be about 1/3 the value of SUM(Sales) instead of a rolling avg. (I tried the method from this: Rolling Average in Power BI Using DAX)
Third line graph
- Good: allows me to use my "fiscal year & fiscal period" x-axis
- Bad: The 3moRollingAvg measure does not work, and just shows the exact same value as SUM(Sales). (I tried the method from this: Rolling 12 Months Average in DAX)
Thanks so much! Sorry for typing so much, but I hope this explains everything ok.
Hi,
To which months do the fiscal periods belong i.e. which months fall in P12. Please prepare a simple table which maps periods to months.
Hi Ashish, thanks for your reply.
Unfortunately the fiscal periods do not line up exactly with months. P1 starts on October 1st, but P2 does not start on December 1st. It's very annoying to work with 😕
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
104 | |
98 | |
90 | |
71 |
User | Count |
---|---|
166 | |
131 | |
129 | |
102 | |
98 |