Calculate daily change several products

[Anonymous]

Hi everyone,

I am very new to Power BI and I am struggling with a problem I can't figure out how to make it work. There are similar problems on this forum, but they often differ in a subtle way, so I can't figure out how to implement the solutions to my specific case.

So my data contains 3 columns:

Product	Date	Amount in kg
Wheat	01/01/22	1000
Corn	01/01/22	800
Beans	01/01/22	1100
Wheat	02/01/22	1100
Corn	02/01/22	950
Beans	02/01/22	1300
Wheat	03/01/22	1250
Corn	03/01/22	1200
Beans	03/01/22	1500

Now I want to add one column that shows the inventory of the product from the previous day ('Amount in kg previous day').

What is the best way to approach this problem?

Thank you!

[tamerj1 · Accepted solution]

Hi @Anonymous 
You can try 

Previous Day Amount =
VAR CurrentDate = Inventory[Date]
VAR T1 =
    CALCULATETABLE ( Inventory, ALLEXCEPT ( Inventory, Inventory[Product] ) )
VAR T2 =
    FILTER ( T1, Inventory[Date] < CurrentDate )
VAR PreviuosDate =
    MAXX ( T2, Inventory[Date] )
VAR T3 =
    FILTER ( T2, Inventory[Date] = PreviuosDate )
RETURN
    SELECTCOLUMNS ( T3, "Previous Day Amount", Inventory[Amount in kg] )

[PiEye · Accepted solution]

Hi E_E

You can use this method to return the previous value for a set value:

Prev =

CALCULATE(

[AmountinKG],

TOPN(

1,

FILTER(

ALLSELECTED( 'Table Name' ),

'Table Name'[Sales Date] < MAX('Table Name'[Sales Date] )

),

'Table Name'[Sales Date],

DESC

)

)

Does this work for you?

Pi

[Anonymous]

Thank you, will also play around with this one!

[tamerj1 · Accepted solution]

Hi @Anonymous 
You can try 

Previous Day Amount =
VAR CurrentDate = Inventory[Date]
VAR T1 =
    CALCULATETABLE ( Inventory, ALLEXCEPT ( Inventory, Inventory[Product] ) )
VAR T2 =
    FILTER ( T1, Inventory[Date] < CurrentDate )
VAR PreviuosDate =
    MAXX ( T2, Inventory[Date] )
VAR T3 =
    FILTER ( T2, Inventory[Date] = PreviuosDate )
RETURN
    SELECTCOLUMNS ( T3, "Previous Day Amount", Inventory[Amount in kg] )

[Anonymous]

That seems to solve it, thank you very much.