Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Friends,
I am looking for a DAX measure to calculate Change in Percentage w.r.t to a particular period by Products. Please suggest me how can we write the optimized DAX for the same. Provided a sample with required output. Baseline is Jan-23 data, this can vary as per business request. Any suggestions are much appreciated.
MY Product Qty %Change
Jan-23 | PROD1 | 100 | |
Feb-23 | PROD1 | 120 | 20% |
Mar-23 | PROD1 | 135 | 35% |
Apr-23 | PROD1 | 140 | 40% |
May-23 | PROD1 | 120 | 20% |
Jun-23 | PROD1 | 100 | 0% |
Jul-23 | PROD1 | 80 | -20% |
Aug-23 | PROD1 | 90 | -10% |
Sep-23 | PROD1 | 75 | -25% |
Oct-23 | PROD1 | 100 | 0% |
Nov-23 | PROD1 | 80 | -20% |
Dec-23 | PROD1 | 65 | -35% |
Thanks
Solved! Go to Solution.
Hi @manojk_pbi ,
Please update the formula of measure as below to get it, please find the details in the attachment.
%Change =
VAR _MY =
SELECTEDVALUE ( 'Table'[MY] )
VAR _product =
SELECTEDVALUE ( 'Table'[Product] )
VAR _preMY =
CALCULATE (
MIN ( 'Table'[MY] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Product] = _product )
)
VAR _preqty =
CALCULATE (
SUM ( 'Table'[Qty] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Product] = _product
&& 'Table'[MY] = _preMY
)
)
RETURN
IF (
_MY = _preMY,
BLANK (),
DIVIDE ( SUM ( 'Table'[Qty] ) - _preqty, _preqty )
)
Best Regards
Thanks for your solution. It's perfect.
Thanks for your solution. It's perfect.
That is a very, very subjective topic especially when sign changes are involved (like in your case).
One approximation is DIVIDE(current-previous, ABS(previous),BLANK())
But at the end of the day you have to decide what is a reasonable number in your scenarios.
Thanks for your input.
I am looking for suggestion on writing measure to calculate % Change .
@lbendlin Thanks for your contribution on this thread.
Hi @manojk_pbi ,
@lbendlin already gave the related formula. If you want to get the expected result base on your sample data, you can create a measure as below to get it:
%Change =
VAR _MY =
SELECTEDVALUE ( 'Table'[MY] )
VAR _preMY =
CALCULATE ( MIN ( 'Table'[MY] ), ALLSELECTED ( 'Table' ) )
VAR _preqty =
CALCULATE (
SUM ( 'Table'[Qty] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[MY] = _preMY )
)
RETURN
IF (
_MY = _preMY,
BLANK (),
DIVIDE ( SUM ( 'Table'[Qty] ) - _preqty, _preqty )
)
Best Regards
@v-yiruan-msft , this is perfect. Thanks for the working sample. If i were had to add Product into filter to calculate % Change for each product what changes to be made here ?
Pls suggest
Hi @manojk_pbi ,
Please update the formula of measure as below to get it, please find the details in the attachment.
%Change =
VAR _MY =
SELECTEDVALUE ( 'Table'[MY] )
VAR _product =
SELECTEDVALUE ( 'Table'[Product] )
VAR _preMY =
CALCULATE (
MIN ( 'Table'[MY] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Product] = _product )
)
VAR _preqty =
CALCULATE (
SUM ( 'Table'[Qty] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Product] = _product
&& 'Table'[MY] = _preMY
)
)
RETURN
IF (
_MY = _preMY,
BLANK (),
DIVIDE ( SUM ( 'Table'[Qty] ) - _preqty, _preqty )
)
Best Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
4 | |
2 | |
1 | |
1 |
User | Count |
---|---|
29 | |
3 | |
3 | |
2 | |
2 |