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

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!