topic How to write DAX for % change in Developer

How to write DAX for % change

manojk_pbi — Mon, 18 Mar 2024 15:45:27 GMT

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

Re: How to write DAX for % change

lbendlin — Mon, 18 Mar 2024 22:03:07 GMT

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.

Re: How to write DAX for % change

manojk_pbi — Tue, 19 Mar 2024 03:26:05 GMT

Thanks for your input.

I am looking for suggestion on writing measure to calculate % Change .

Re: How to write DAX for % change

Anonymous — Tue, 19 Mar 2024 08:15:01 GMT

@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

Re: How to write DAX for % change

manojk_pbi — Tue, 19 Mar 2024 08:27:17 GMT

@Anonymous , 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

Re: How to write DAX for % change

Anonymous — Tue, 19 Mar 2024 08:43:50 GMT

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

Re: How to write DAX for % change

manojk_pbi — Tue, 19 Mar 2024 08:56:21 GMT

Thanks for your solution. It's perfect.

Re: How to write DAX for % change

manojk_pbi — Tue, 19 Mar 2024 08:56:31 GMT

Thanks for your solution. It's perfect.