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 timeframe based on a relative "between" date slicer like the one below.

The end goal is to show a column chart that displays the change in units on the Y axis and the product type on the X axis, as shown below. For example, if the date slicer shows 2/1/2019 to 4/1/2019, the "bus" category should show a net change of 65 (i.e. 86-21=65).

 

How can I set this up? Is it possible to create a measure that responds to the dates in the slicer?

Thank you!

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

14 Replies

  • 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
)
  • jtownsend21's avatar
    jtownsend21
    Responsive Resident
    7 years ago

    Anonymous, I know I am late to the party on this one, but here is a slightly different version which might help if you find yourself facing some performance issues with the filter statements. 

    NET CHANGE TEST = 
VAR _LAST = 
    CALCULATE(
        SUM('Inventory'[Units]),
        LASTDATE('Date'[Date])
    )
VAR _FIRST = 
    CALCULATE(
        SUM('Inventory'[Units]),
        FIRSTDATE('Date'[Date])
    )

RETURN 
_LAST - _FIRST
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Appreciate it, thank you!

  • hejszyszki's avatar
    hejszyszki
    Frequent Visitor
    5 years ago

    Hi guys, what about when new product appears? I am struggling to change formula to include also this aspect. Do you have any ideas? I think i understand why its not included in formula output ( cuz its not finding product name value to subtract) but no idea how to change to do so.
    MFelix edhans any ideas?

    Best Regards,
    PP

    • MFelix's avatar
      MFelix
      Super User
      5 years ago

      HI hejszyszki ,

       

      Measures are based in context so if the product is added to the visualization the calculation shoud be done has expected however what probably is hapenning is since you only have the value on the first month the variation gives a blank value and does not retur any values in the visualzation. 

      • hejszyszki's avatar
        hejszyszki
        Frequent Visitor
        5 years ago

        Sounds reasonable MFelix , so my idea now to resolve this is to update the formula with something like IF blank then return "end month" value? Am i thinking correctly?
        IAR Comparison=
        VAR StartDate = MIN ( 'IAR Extract'[Version] )
        VAR EndDate = MAX ( 'IAR Extract'[Version] )
        RETURN CALCULATE ( SUM ( 'IAR Extract'[Financial Risk Value PUP] ),
        FILTER (ALLSELECTED('IAR Extract'[Version] ), 'IAR Extract'[Version] = EndDate )
        ) - CALCULATE (
        SUM ( 'IAR Extract'[Financial Risk Value PUP] ),
        FILTER ( ALLSELECTED ( 'IAR Extract'[Version] ), 'IAR Extract'[Version] = StartDate ) )

        And to refer, thats the formula ive been using...

        Best regards
        PP