Hello,
I am trying to calculate in DAX a predictive inventory. The way as per attached formula does not work the way I need it - I need the previous calculated inventory (from the previous month) to serve as a basis for the calculation of the next month:
Can anybody give me a hint on where to start to tackle this?
Thanks!
Kind regards
Valeria
Predicted Inv test.pbix (59.9 KB)
Hi @valeriabreveglieri ,
I believe you want to pass Predicted Inventroy from previous row
You can try New measure
New Measure =
VAR CurrentYearMonth = MAX ( 'Date'[Year Month] )
VAR PreviousYearMonth = CONVERT(If (RIGHT(CurrentYearMonth,2) <> "01", MAX ( 'Date'[Year Month] )-1,MAX ( 'Date'[Year Month] )-100+11),STRING)
VAR PreviousInventory =
CALCULATE(
[Predicted Inventory],
'Date'[Year Month] = PreviousYearMonth
)
Return PreviousInventory
Hope it helps.
Greg
April 24, 2024, 12:01am
3
Hi @valeriabreveglieri
The [Previous Row Value] DAX pattern may be of some use.
Often in a Power BI visual one wants to get the value from the previous row to use in a calculation in the current row (e.g., to see if there’s a change between the previous value and the current value). The Previous Row Value DAX pattern can be of help when faced with this issue.
As with most situations in Power BI, there are many ways to solve the same problem: here are a couple:
Method 1: Add DAX Index Column
1-Add a [DAX Index] calculated column to your table
DAX Index =
// DAX PATTERN …
Hope it helps.
Greg
2 Likes
Roger
April 29, 2024, 12:00am
4
Valeria
PreviousInventory =
VAR CurrentYearMonth = MAX ( ‘Date’[Year Month] )
VAR LastKnownInventoryDate =
CALCULATE (
MAX ( ‘Inventory’[MonthYear] ),
NOT ISBLANK ( Inventory[TotalInventoryEOM] ),
‘Date’[Year Month] < CurrentYearMonth
)
VAR PreviousInventory =
CALCULATE(
SUM ( Inventory[TotalInventoryEOM] ),
‘Date’[Year Month] = LastKnownInventoryDate
)
VAR NextSupplyDemand =
CALCULATE(
[Total Supply#] - [Total Demand#],
‘Date’[Year Month] > LastKnownInventoryDate,
‘Date’[Year Month] <= CurrentYearMonth
)
VAR Result = PreviousInventory + NextSupplyDemand
RETURN
PreviousInventory ( error)
your are returning previousInventory
change this in Result
Roger
1 Like
Thanks @Roger ! I had not thought about it this way - works wonderfully!
Roger
May 2, 2024, 11:24pm
6
Valeria
As the total of your viual doesn’t make any sence, it’s is beter to wrap the measure in an if() and use the
hasonevalue() to hide the total line of the visual. ( see the screen shot )
kind regards
Roger