DAX Week to week difference to calculate an average usage

[mdennis05]

I am developing an inventory dashboard. Each week there will be a weekly count. I would like to calculation the average usage. I know I need to calculate the difference from each week's count for an item then average this. However, I am getting nowhere on it.

Last Week = CALCULATE(DISTINCTCOUNT('Count'[Product Name]), 'Calendar'[Week Num] = MAX('Calendar'[Week Num])-1)


Weekly Average = AVERAGEX(SUMMARIZE('Count','Count'[Quantity], "toAverage",[Last Week]),[Last Week])

This is returning 1 for values. which is correct then blank for others that should have a value.

My end goal is to use this as a minimum ordering level for 2 months. Multiplying the average weekly usage by 8.

[bhanu_gautam]

@mdennis05 Try using

Calculate the previous week's count:

LastWeekCount =
CALCULATE(
SUM('Count'[Quantity]),
'Calendar'[Week Num] = MAX('Calendar'[Week Num]) - 1
)

Calculate the difference from the previous week:

DAX
WeekDifference =
SUM('Count'[Quantity]) - [LastWeekCount]

Calculate the average of these differences:

DAX
AverageWeeklyUsage =
AVERAGEX(
SUMMARIZE(
'Count',
'Calendar'[Week Num],
"WeeklyDiff", [WeekDifference]
),
[WeeklyDiff]
)

Calculate the minimum ordering level for 2 months (8 weeks):

MinOrderingLevel = [AverageWeeklyUsage] * 8

[some_bih]

Hi @mdennis05 try v2 below

Last Week v2 =

VAR __week_filt= MAX('Calendar'[Week Num])-1

CALCULATE(DISTINCTCOUNT('Count'[Product Name]), 'Calendar'[Week Num] = __week_filt)

Weekly Average = AVERAGEX(SUMMARIZE('Count','Count'[Quantity], "toAverage",[Last Week]),[Last Week])