Use previous measure row result to calculate the result of the following row in same measure

[h2bi]

Hello

I found a huge problem applying a simple formula into Power BI DAX measure

I have this table, YearMonthNum is like an Index column

I want to calculate what comes after 20240405 which is a forecast period

So the logic is simple

= B5 - C6 so 17-2 = 15 
= B6 -C7 so 15 (the previous result) - 3 = 12

and so on

The measure must calculate the difference between the previous result and the current Quantity

I tried many methods like :

Measure =
var inventory_pm= calculate( sum(Inventory), 'Table'[YearMonthNum]= Max('Table'[YearMonthNum])-1 )
var quantity_cm = calculate( sum(Quantity), 'Table'[YearMonthNum]= Max('Table'[YearMonthNum]) )

return

inventory_pm - quantity_cm 

It calculates it untill there is no Inventory so it does'nt take the previous measure result, it takes always the previous Inventory cell

I highly apppreciate your assitance on this, i am stuck on this since a while

Best regards!

[smpa01]

@h2bi  you can surely do this

_Measure = 
VAR lastBlank =
    IF (
        MAX ( 'Table'[Inventory] ) = BLANK (),
        MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Inventory] <> BLANK () ),
            'Table'[YearNum]
        )
    )
VAR rnt =
    IF (
        NOT ( ISBLANK ( lastBlank ) ),
        VAR _right =
            CALCULATE (
                SUM ( 'Table'[Qt] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[YearNum] > lastBlank + 1
                        && 'Table'[YearNum] <= MAX ( 'Table'[YearNum] )
                )
            )
        VAR _left =
            CALCULATE (
                SUM ( 'Table'[Inventory] ),
                FILTER ( ALL ( 'Table' ), 'Table'[YearNum] = lastBlank )
            )
        RETURN
            _left - _right,
        SUM ( 'Table'[Inventory] )
    )
RETURN
    rnt

[h2bi]

Nice one, thanks a lot man you are a genius!

But what if instead of the column Inventory, i calculate it over a measure [Inventory PM] which is basically the inventory of previous month

Like this, Inventory PM is my measure

i tried to replace the 'Table'[Inventory] column by my [Inventory PM] measure and instead of using YearNum of Table i am using YearNum of Calendary table related with Table, so it would look like this:

_Measure = 
VAR lastBlank =
    IF (
        [Inventory PM] = BLANK (),
        MAXX (
            FILTER ( ALL ( 'Calendary' ), [Inventory PM] <> BLANK () ),
            'Calendary'[YearNum]
        )
    )
VAR rnt =
    IF (
        NOT ( ISBLANK ( lastBlank ) ),
        VAR _right =
            CALCULATE (
                SUM ( 'Table'[Qt] ),
                FILTER (
                    ALL ( 'Calendary' ),
                    'Calendary'[YearNum] > lastBlank + 1
                        && 'Calendary'[YearNum] <= MAX ( 'Calendary'[YearNum] )
                )
            )
        VAR _left =
            CALCULATE (
                [Inventory PM],
                FILTER ( ALL ( 'Calendary' ), 'Calendary'[YearNum] = lastBlank )
            )
        RETURN
            _left - _right,
        [Inventory PM]
    )
RETURN
    rnt

But now with this modification i am geetting a weird situation, wher it takes the last value of [Inventory PM] and rests from it SUM(Qty) so:

17-3

17-2

17-4 ....

It is not taking the previous row result of the measure 

Could you please suggest a solution?

Thanks a lot!

[smpa01]

@h2bi  provide sample pbix please

[HB2PBI]

Hello, this is me but with different account

So this is the sample file

ADS column is Quantity column

I want to perform it over the measure [Inventory PM] instead of SUM('Table'[Inventory]), and use YearMnthNum of a Calendary table not the same Table

https://drive.google.com/file/d/1ZJ_FYpLXR9Q3-KbusNBA3hBu6IBXZilt/view?usp=sharing

I would really appreciate it and thanks in advance !

[smpa01 · Accepted solution]

@h2bi @HB2PBI please try this

_Measure = 
VAR baseTbl =
    ADDCOLUMNS (
        SUMMARIZE ( ALL ( 'Calendary' ), Calendary[YearMonthNum] ),
        "@prev", [inventoryPrev]
    )
RETURN
    IF (
        [inventoryCurrent] = BLANK ()
            && [inventoryPrev] = BLANK (),
        VAR _lastBlank =
            MAXX ( FILTER ( baseTbl, [@prev] <> BLANK () ), [YearMonthNum] )
        VAR _left =
            MAXX ( FILTER ( baseTbl, [YearMonthNum] = _lastBlank ), [@prev] )
        VAR _right =
            IF (
                [inventoryCurrent] = BLANK ()
                    && [inventoryPrev] = BLANK (),
                CALCULATE (
                    [adsCurrent],
                    FILTER (
                        ALL ( Calendary ),
                        Calendary[YearMonthNum] > _lastBlank + 1
                            && Calendary[YearMonthNum] <= MAX ( Calendary[YearMonthNum] )
                    )
                )
            )
        RETURN
            _left - _right,
        [inventoryPrev]
    )

[ray_aramburo]

You can't store values on measures or even in visual calculations so the Inventory sum for your rows in blank will always be blank because that's how they are evaluated at the moment of processing your expressions.

See for example:

The fifth row gives you the 15 you are expecting but on the next row is giving you -3 because at the moment the expression is being evaluated, the previous value in the cell is a blank, not 15. In order for it to be 15 you would need to write again the same calculation but as new and repeat that until you don't have blanks. Not an efficient thing to do.