Forum Discussion
Calculating Past and Future Inventory Value based on today's inventory value
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?
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
2 Replies
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.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
