Average of Last n Months Daily Granularity

kman5554 — Sat, 28 Jan 2023 01:40:17 GMT

Hi all,

Struggling with a problem for a couple of days so thought I would ask here.

I have a table of prices similar to the following (which spans 15 years):

Date	Price
1 Apr 2021	62.1876
2 Apr 2021	62.1876
3 Apr 2021	62.1876
4 Apr 2021	62.1876
5 Apr 2021	62.1876
6 Apr 2021	62.3674
7 Apr 2021	61.0276
8 Apr 2021	62.002
9 Apr 2021	62.2688
10 Apr 2021	62.2688
11 Apr 2021	62.2688
12 Apr 2021	62.5704
13 Apr 2021	63.0924
14 Apr 2021	65.6676
15 Apr 2021	65.5342

What I need to do is calculate the average for each individual month, then the average of the 9 previous months of each of those individual averaged months and display the data in daily format (so the value for each day in a given motnh will be the same). This is so I can plot this on a day granularity chart. To clarify, I've broken it doen into the main steps:

Obtaining average price for each month

Calculating the average of the previous 9 months for each given row (excluding current month)

Date	Avg Monthly Price	Avg Prive over last 9 Months
Jan-20	63.90047097	(NA)
Feb-20	55.5744	(NA)
Mar-20	32.20683871	(NA)
Apr-20	19.5141	(NA)
May-20	28.78334194	(NA)
Jun-20	40.25451333	(NA)
Jul-20	43.31926452	(NA)
Aug-20	44.56663871	(NA)
Sep-20	40.84572667	(NA)
Oct-20	40.20878065	40.99614387
Nov-20	42.44034	38.36373384
Dec-20	50.01433548	38.77139445
Jan-21	54.47116774	38.215388
Feb-21	62.3848	40.4418209
Mar-21	65.83935484	44.7288909
Apr-21	64.33843333	48.43449219
May-21	68.55506452	50.84288419

Output should be as follows (as you can see April 2021 has the same values for each day which alligns with the value above in orange)

Date	Price	Average Price over last 9 months
1 Apr 2021	62.1876	48.43449219
2 Apr 2021	62.1876	48.43449219
3 Apr 2021	62.1876	48.43449219
4 Apr 2021	62.1876	48.43449219
5 Apr 2021	62.1876	48.43449219

I've been trying many ways of doing this without success. Unsure if it requires a calculated column or if a measure can do this. In excel it is straightforward as there are functions such as offset with the height component but in PowerBI it seems to be more challenging. Any hlep would be greatly appreciated!

Re: Average of Last n Months Daily Granularity

amitchandak — Sat, 28 Jan 2023 02:27:22 GMT

kman5554 , Assume you already have a measure monthly price

monthly price = Average(Table[Price]) //If price is not a measure

with help from date table

12 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),[monthly price])
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

You can use Numeric Parameter to make 12 dynamic

Example here - https://youtu.be/cN8AO3_vmlY?t=26514

You can also use window function

https://youtu.be/cN8AO3_vmlY?t=33360

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

Re: Average of Last n Months Daily Granularity

kman5554 — Sun, 29 Jan 2023 02:38:17 GMT

Thanks for the help Amitchandak.

The measure you provided only works against monthly filtering. If I bring the measure against a daily date context the numbers are variable day to day.

topic Re: Average of Last n Months Daily Granularity in DAX Commands and Tips

Average of Last n Months Daily Granularity

Re: Average of Last n Months Daily Granularity

Re: Average of Last n Months Daily Granularity