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

MFelix's avatar
MFelix
Super User
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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Works perfectly. Thank you 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