Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Subtract two unit quantities based on date slicer

I want to subtract the amount of units in inventory for different product types over a given timeframe. I've created the following dummy data to illustrate: The user should be able to select a...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous ,

     

    Add the following measure to your report:

     

    Inventory Variation =
VAR StartDate =
    MIN ( Inventory[Date] )
VAR EndDate =
    MAX ( Inventory[Date] )
RETURN
    CALCULATE (
        SUM ( Inventory[Inventory (Units)] );
        FILTER ( ALLSELECTED ( Inventory[Date] ); Inventory[Date] = EndDate )
    )
        - CALCULATE (
            SUM ( Inventory[Inventory (Units)] );
            FILTER ( ALLSELECTED ( Inventory[Date] ); Inventory[Date] = StartDate )
        )

    Final result is as follows:

    Regards,

    MFelix

edhans's avatar
edhans
Community Champion
7 years ago

Anonymous , the solution provided by MFelix works if your slicer is based on your dates in the inventory table. If you use a slicer based on dates in a Date table, which is the direction I took, the following would work, assuming your inventory table had one entry per month per category, as your sample data did.

 

Inventory Change = 
VAR FirstMonth =
    CALCULATE(
        MIN(Dates[Month]),
        ALLSELECTED(Dates[Date])
    )
VAR SecondMonth =
    CALCULATE(
        MAX(Dates[Month]),
        ALLSELECTED(Dates[Date])
    )
RETURN
CALCULATE(
    MAX('Inventory Levels'[Inventory]),
    Dates[Month] = SecondMonth
)
-
CALCULATE(
    MAX('Inventory Levels'[Inventory]),
    Dates[Month] = FirstMonth
)
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Good clarification, thank you edhans