Predited Inventory - recursive

DAX
1

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:

image
image918×541 142 KB

Can anybody give me a hint on where to start to tackle this?
Thanks!
Kind regards
Valeria

Predicted Inv test.pbix (59.9 KB)

2

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.

3

Hi @valeriabreveglieri

The [Previous Row Value] DAX pattern may be of some use.

Hope it helps.
Greg

2 Likes
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
5

Thanks @Roger ! I had not thought about it this way - works wonderfully!

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

image