Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
@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/3395....
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
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
87 | |
87 | |
52 | |
37 | |
23 |