Subtract two unit quantities based on date slicer

[derickson091]

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!

[hejszyszki]

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]

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]

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

[MFelix]

HI @hejszyszki 

You just need to use coalesce something similar to:

IAR Comparison =
VAR StartDate =
    MIN ( 'IAR Extract'[Version] )
VAR EndDate =
    MAX ( 'IAR Extract'[Version] )
RETURN
    COALESCE (
        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
                )
            ),
        CALCULATE (
            SUM ( 'IAR Extract'[Financial Risk Value PUP] ),
            FILTER (
                ALLSELECTED ( 'IAR Extract'[Version] ),
                'IAR Extract'[Version] = EndDate
            )
        )
    )

[hejszyszki]

Unfortunetly it is outputting 0 when new product appears, also 0 when it dissapear. My goal is to make a comparison of changes, so lets say inventory in January of X product is 10 and in February is 0 - then graph goes -10 and when Y product in January is 0 and in Feb 10 graph output +10 ---> The if statement does not work because for new apperance it outputs a value X and for dissapearance it outputs X (instead of -X)...

[hejszyszki]

Guys, @MFelix, any ideas for that? Or should i close the topic🤔? 

Best regards

[MFelix]

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[hejszyszki]

Thanks @MFelix , I shared file in private message 

Best regards

[jtownsend21]

@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]

Appreciate it, thank you!

[MFelix · Accepted solution]

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:

Regards,

MFelix

[derickson091]

Works perfectly. Thank you @MFelix  !

[edhans]

@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]

Good clarification, thank you @edhans