cancel
Showing results for
Did you mean:
derickson091 Frequent Visitor

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!

1 ACCEPTED SOLUTION

Accepted Solutions Super User

Re: Subtract two unit quantities based on date slicer

```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

Proud to be a Datanaut!

6 REPLIES 6 Super User

Re: Subtract two unit quantities based on date slicer

```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

Proud to be a Datanaut!

derickson091 Frequent Visitor

Re: Subtract two unit quantities based on date slicer

Works perfectly. Thank you @MFelix  !

Highlighted
edhans New Contributor

Re: Subtract two unit quantities based on date slicer

@derickson091 , 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
)```
derickson091 Frequent Visitor

Re: Subtract two unit quantities based on date slicer

Good clarification, thank you @edhans

jtownsend21 Member

Re: Subtract two unit quantities based on date slicer

@derickson091, 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```
derickson091 Frequent Visitor

Re: Subtract two unit quantities based on date slicer

Appreciate it, thank you!