cancel
Showing results for
Did you mean:
Frequent Visitor

## Trailing Averages (of Monthly Average)

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.

``````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

3 REPLIES 3
Super User

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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?

Frequent Visitor

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

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors