cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
derickson091 Frequent Visitor
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:

Capture121.PNG

The user should be able to select a timeframe based on a relative "between" date slicer like the one below.

Capture222.PNG

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
Highlighted
Super User
Super User

Re: Subtract two unit quantities based on date slicer

Hi @derickson091 ,

 

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:

inventory difference.png

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




6 REPLIES 6
Highlighted
Super User
Super User

Re: Subtract two unit quantities based on date slicer

Hi @derickson091 ,

 

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:

inventory difference.png

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




derickson091 Frequent Visitor
Frequent Visitor

Re: Subtract two unit quantities based on date slicer

Works perfectly. Thank you @MFelix  !

edhans New Contributor
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
Frequent Visitor

Re: Subtract two unit quantities based on date slicer

Good clarification, thank you @edhans 

jtownsend21 Member
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
Frequent Visitor

Re: Subtract two unit quantities based on date slicer

Appreciate it, thank you!