Calculating Past and Future Inventory Value based on today's inventory value

[power2]

Hello everyone,

I built a power bi report that can show the inventory value now. And it can also show what was received each day and what was processed. So, let's say today's inventory value is $100,000 and we received $10,000 and processed $20,000 today so that would mean our inventory was $110,000 yesterday right!

I want to create a system where it will back track and find the inventry value of each day in the last 10 months and it will also start calculating it from now on in the future. All this will be based off of today's inventory value.

Any help?

[power2]

Here is a visual representation of what I am looking for. Obviolusy, if I did this on excel, I would just drag up and it would fill in the value based on the easy logic that I did (Current Value - In + Out)

I need to implement this in Power Bi with a DAX code.

[Anonymous · Accepted solution]

Hi @power2 ,

You can follow the steps below to get it, please find the details in the attachment.

1. Add index column in Power Query Editor

= Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)

2. Create a calculated column as below

Expected Column = 
VAR _inventory =
    CALCULATE ( SUM ( 'Table'[Inventory Value] ), ALL ( 'Table' ) )
RETURN
    CALCULATE (
        _inventory + SUM ( 'Table'[OUT] )
            - SUM ( 'Table'[IN] ),
        FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) - 1 )
    )

Best Regards