topic Re: Calculating Past and Future Inventory Value based on today's inventory value in Developer

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

power2 — Mon, 08 Apr 2024 17:33:03 GMT

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?

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

power2 — Mon, 08 Apr 2024 18:02:29 GMT

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.

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

Anonymous — Tue, 09 Apr 2024 06:48:51 GMT

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