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

[castewart]

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

[v-yiruan-msft]

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

[castewart]

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.

[v-yiruan-msft]

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

[castewart]

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.

[Ashish_Mathur]

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.

[castewart]

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 😕