Dax measure that uses previous week value in calculation

nd888 — Fri, 04 Aug 2023 15:44:52 GMT

Hello, I am trying to create a measure that uses a previous week's value in the current week's value calculation.

My variables are onhand inventory, production forecast, sales Forecast. Essentially the formula for the current week is:

inventory forecast = onhand inventory - sales forecast + production forecast.

Inventory Forecast This Week = CALCULATE([Remaining QTY Sum] - [Sales Forecast] + 'Prod Forecast Entries'[Forecast Quantity] , filter( ALLSELECTED('Date Calculate Today or After'[Week Start]), 'Date Calculate Today or After'[Week Start] = MIN('Date Calculate Today'[Week Start]) ) )

Then for subsequest weeks I need to reference the value of the previous week as my onhand inventory. So the formula would essentially be:

Inventory forecast = Last Week Onhand Inventory - Sales Forecast + Production Forecast

This is what I have so far but I can't figure out the "Inventory Forecast Last Week" part of the equation.

Inventory Forecast = if(min('Date Calculate Today'[Week Start]) = min('Date Calculate Today or After'[Week Start]) , [Inventory Forecast This Week] , "Inventory Forecast Last Week" - [Sales Forecast] +'Prod Forecast Entries'[Forecast Quantity] )

Any help here would be greatly appreciated.

Thank you.

Re: Dax measure that uses previous week value in calculation

Greg_Deckler — Fri, 04 Aug 2023 15:46:32 GMT

nd888 See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous

topic Re: Dax measure that uses previous week value in calculation in DAX Commands and Tips

Dax measure that uses previous week value in calculation

Re: Dax measure that uses previous week value in calculation