Forum Discussion

power2's avatar
power2
Frequent Visitor
2 years ago
Solved

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 $...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

power2's avatar
power2
Frequent Visitor
2 years ago


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's avatar
Anonymous
Not applicable
2 years ago

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