Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsSounds 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUnfortunetly 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)...
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
Proud to be a Super User!
Check out my blog: Power BI em Português@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!
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@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 )
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingStarting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
60 | |
57 |