recalculate stock based on difference between stock and usage on the previous date

[Anonymous]

Hello,

can you please help me to find solution for the next need.

I need to recalculate stock based on difference between stock and usage on the previous date:

current stock = current stock + (previous stock - previous usage)

For example:

05-12-2021 stock should be 160876 + (52074 - 229540) = -16590

and so on.

I could not do it with calculated columns, it looks like a recoursion I believe but I am not sure.

[Icey]

Hi @Anonymous ,

You can create measures like [Measure 1] or [Measure 2] like so:

Measure 1 = 
VAR CurrentProductCode_ =
    MAX ( 'Table'[product_code] )
VAR CurrentDate_ =
    MAX ( 'Table'[date] )
VAR PreviousDate_ =
    CALCULATE (
        MAX ( 'Table'[date] ),
        'Table'[date] < CurrentDate_,
        'Table'[product_code] = CurrentProductCode_
    )
VAR CurrentStock_ =
    SUM ( 'Table'[stock] )
VAR PreviousStoke_ =
    CALCULATE (
        SUM ( 'Table'[stock] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[product_code] = CurrentProductCode_
                && 'Table'[date] = PreviousDate_
        )
    )
VAR PreviousUsage_ =
    CALCULATE (
        SUM ( 'Table'[usage] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[product_code] = CurrentProductCode_
                && 'Table'[date] = PreviousDate_
        )
    )
RETURN
    CurrentStock_ + ( PreviousStoke_ - PreviousUsage_ )

Measure 2 = 
VAR CurrentProductCode_ =
    MAX ( 'Table'[product_code] )
VAR CurrentDate_ =
    MAX ( 'Table'[date] )
VAR PreviousDate_ =
    CALCULATE (
        MAX ( 'Table'[date] ),
        'Table'[date] < CurrentDate_,
        'Table'[product_code] = CurrentProductCode_
    )
VAR CurrentStock_ =
    SUM ( 'Table'[stock] )
VAR PreviousStoke_ =
    LOOKUPVALUE (
        'Table'[stock],
        'Table'[product_code], CurrentProductCode_,
        'Table'[date], PreviousDate_
    )
VAR PreviousUsage_ =
    LOOKUPVALUE (
        'Table'[usage],
        'Table'[product_code], CurrentProductCode_,
        'Table'[date], PreviousDate_
    )
RETURN
    CurrentStock_ + ( PreviousStoke_ - PreviousUsage_ )

Or columns like so:

Column 1 = 
VAR CurrentProductCode_ = 'Table'[product_code]
VAR CurrentDate_ = 'Table'[date]
VAR PreviousDate_ =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            'Table',
            'Table'[date] < CurrentDate_
                && 'Table'[product_code] = CurrentProductCode_
        )
    )
VAR CurrentStock_ = 'Table'[stock]
VAR PreviousStoke_ =
    CALCULATE (
        SUM ( 'Table'[stock] ),
        FILTER (
            'Table',
            'Table'[product_code] = CurrentProductCode_
                && 'Table'[date] = PreviousDate_
        )
    )
VAR PreviousUsage_ =
    CALCULATE (
        SUM ( 'Table'[usage] ),
        FILTER (
            'Table',
            'Table'[product_code] = CurrentProductCode_
                && 'Table'[date] = PreviousDate_
        )
    )
RETURN
    CurrentStock_ + ( PreviousStoke_ - PreviousUsage_ )

Column 2 = 
VAR CurrentProductCode_ = 'Table'[product_code]
VAR CurrentDate_ = 'Table'[date]
VAR PreviousDate_ =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            'Table',
            'Table'[date] < CurrentDate_
                && 'Table'[product_code] = CurrentProductCode_
        )
    )
VAR CurrentStock_ = 'Table'[stock]
VAR PreviousStoke_ =
    LOOKUPVALUE (
        'Table'[stock],
        'Table'[product_code], CurrentProductCode_,
        'Table'[date], PreviousDate_
    )
VAR PreviousUsage_ =
    LOOKUPVALUE (
        'Table'[usage],
        'Table'[product_code], CurrentProductCode_,
        'Table'[date], PreviousDate_
    )
RETURN
    CurrentStock_ + ( PreviousStoke_ - PreviousUsage_ )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Icey,

thank you for your reply. But that does not calculate stock properly. 

For the latest dates we need take the calculated stock, but not initial one from the column 'stock'.
There would be 2 possible solutions, but as I can understand Power BI does not support them:

1. Rewrite calculated stock in the column 'stock'

2. To have a global variable where we store the calculated stock during iteration of the table for each product.

What is your opinion?

[Icey · Accepted solution]

Hi @Anonymous ,

Sorry, I misunderstood before. Is the following result what you want?

If it is, we can change our calculation to get the same result. For example,

E2 = C2

E3 = C3+E2-D2

⋙ E3 = C3+C2-D2

E4 = C4+E3-D3

⋙ E4 = C4 + (C3+E2-D2) -D3 =C4 + (C3+C2-D2) -D3 = (C4 + C3+C2) - (D3+D2)

Then, create a column like so:

Can it work?

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Icey,

thank you very much - that is what I need.
I have never used the function EARLIER() before.
Perfect!