Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
castewart
Frequent Visitor

Rolling Average of a Sum using fiscal year/period (without date)

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.

pbi problem rolling avg.jpg

 

Is there any way I can get to something like this using DAX?

example rolling avg.jpg

 

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 IDFiscal YearFiscal PeriodValueIndustry
34152021P0194a
97622021P017b
92872021P0183c
28032021P0296d
13782021P0291a
51352021P02100c
17132021P0368a
47152021P0388b
74102021P0492c
41082021P0473d
75372021P0445a
55922021P0541c
79782021P051a
61732021P068b
69982021P0621c
18122021P0649d
26952021P0697a
72682021P0769c
75502021P0711a
85602021P0880b
96332021P0819c
68842021P0881d
94402021P0936a
88752021P0942c
52542021P1058a
59992021P1049b
31762021P1037c
11762021P1132d
30222021P1183a
24032021P1252c
27952021P1231a
37402022P1284b
53172022P120c
60172022P0199d
19472022P0178a
79232022P0130c
50242022P0276a
85782022P0254b
37332022P0245c
20052022P0327d
53052022P0331a
90272022P0374c
6 REPLIES 6
v-yiruan-msft
Community Support
Community Support

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.

yingyinr_0-1642499901385.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Screenshot 2022-01-18 165003.jpg

 

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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)

 

Screenshot 2022-01-25 161303.jpg

 

Thanks so much!  Sorry for typing so much, but I hope this explains everything ok.

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 😕

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.