Forum Discussion
How to write DAX for % change
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
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
7 Replies
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
Thanks for your solution. It's perfect.
Thanks for your solution. It's perfect.
