Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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 |
---|---|
54 | |
48 | |
19 | |
16 | |
15 |
User | Count |
---|---|
115 | |
42 | |
41 | |
26 | |
22 |