Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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:

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

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

14 REPLIES 14
hejszyszki
Frequent Visitor

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

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. 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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)...

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

Best regards

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.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks @MFelix , I shared file in private message 

Best regards

jtownsend21
Responsive Resident
Responsive Resident

@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

Appreciate it, thank you!

MFelix
Super User
Super User

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Works perfectly. Thank you @MFelix  !

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


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Good clarification, thank you @edhans 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.