Trailing Averages (of Monthly Average)

[mpoche]

Hi Power Bi Community,

I am trying to calcuate the trailing 12 month's average of the monthly average (Beginning Inventory + Ending Inventory / 2). This is to calculate the Inventory Turnover formula (Cost of Goods Sold / Average Inventory). This is always based on the trailing 12 months.

I start with calculating the monthly average:

Monthly Avg = 
CALCULATE (
    [Inventory Value],
    DATESINPERIOD ( Date_Lookup[Date], LASTDATE ( Date_Lookup[Date] ), -2, MONTH )
) / 2

 Then I use the following measure to try and calculate the trailing 12 months average of the monthly averages:

TTM Monthly Avg. = 
CALCULATE(
    [Monthly Avg],
    DATESINPERIOD(Date_Lookup[Date],LASTDATE(Date_Lookup[Date]),-12,MONTH
    )
) / 12

However, that calcuates the applicable monthly average and divides only that month by 12 and does not sum up the trailing 12 monthly averages.

Any help is appreciated.

Thanks

[Greg_Deckler]

@mpoche Try this: Better Rolling Average - Microsoft Power BI Community

[mpoche]

But will this only do the rolling average of my inventory value? Or will it do the average of the monthly average?

In the _StartDate variable, how do you make the Day always the last day of that month?

[mpoche]

Here is a sample of what the outcome is of the previous 12 months:

End of Month	Inventory Value	Monthly Avg	TTM Monthly Avg.
10/31/2021	$8,474,154.17		$668,532.56
11/30/2021	$8,289,281.09	$8,381,717.63	$698,476.47
12/31/2021	$8,064,217.09	$8,176,749.09	$681,395.76
1/31/2022	$8,092,898.27	$8,078,557.68	$673,213.14
2/28/2022	$8,844,389.56	$8,468,643.91	$705,720.33
3/31/2022	$9,994,850.58	$9,419,620.07	$784,968.34
4/30/2022	$10,040,748.89	$10,017,799.73	$834,816.64
5/31/2022	$9,214,557.29	$9,627,653.09	$802,304.42
6/30/2022	$9,373,298.02	$9,293,927.65	$774,493.97
7/31/2022	$10,339,076.95	$9,856,187.48	$821,348.96
8/31/2022	$12,050,477.58	$11,194,777.26	$932,898.11
9/30/2022	$11,826,241.00	$11,938,359.29	$994,863.27
10/31/2022	$11,314,526.98	$11,570,383.99	$964,198.67