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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

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

14 REPLIES 14
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

Super User

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

Frequent Visitor

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

Super User

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

Frequent Visitor

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

Frequent Visitor

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

Best regards

Super User

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

Frequent Visitor

Thanks @MFelix , I shared file in private message

Best regards

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

Appreciate it, thank you!

Super User

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

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

Frequent Visitor

Works perfectly. Thank you @MFelix  !

Super User

@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
Frequent Visitor

Good clarification, thank you @edhans

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors