topic Re: How to get Change in Percentage with respect to Base line data in DAX Commands and Tips

How to get Change in Percentage with respect to Base line data

manojk_pbi — Mon, 18 Mar 2024 15:43:50 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 get Change in Percentage with respect to Base line data

bhanu_gautam — Mon, 18 Mar 2024 16:15:52 GMT

manojk_pbi , You can try using below measure

%Change =
VAR BaselineQty = CALCULATE(SUM(Table1[Qty]), FILTER(ALL(Table1), Table1[MY] = "Jan-23"))
VAR CurrentQty = SUM(Table1[Qty])
RETURN
IF(
BaselineQty <> 0,
DIVIDE(CurrentQty - BaselineQty, BaselineQty),
BLANK()
)

Please accept as solution and give kudos if it helps

Re: How to get Change in Percentage with respect to Base line data

manojk_pbi — Tue, 19 Mar 2024 03:24:01 GMT

Hi bhanu_gautam , thanks for quick response.

Where can i use this measure, should i be creating the column in the table or use the measure directly into charts ? How will this consider product filter ? Data i am looking at Product level

Re: How to get Change in Percentage with respect to Base line data

bhanu_gautam — Tue, 19 Mar 2024 16:13:30 GMT

Try creating measure and pull that into visuals

Re: How to get Change in Percentage with respect to Base line data

Anonymous — Fri, 22 Mar 2024 09:38:53 GMT

Hi, manojk_pbi

Your solution is great, bhanu_gautam . It worked like a charm! Has your problem been solved?

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

Re: How to get Change in Percentage with respect to Base line data

Anonymous — Fri, 29 Mar 2024 04:20:42 GMT

Hi, manojk_pbi

Based on your description, I used the following sample data:

I created a table visual as shown in the image below:

I created a measure with the following DAX:

Percentage = VAR _minday = CALCULATE ( MIN ( 'Table'[MY] ), ALL ( 'Table' ) ) VAR _mindayQty = CALCULATE ( MIN ( 'Table'[Qty] ), FILTER ( ALL ( 'Table' ), 'Table'[MY] = _minday ) ) RETURN IF ( MIN ( 'Table'[MY] ) = _minday, BLANK (), DIVIDE ( SUM ( 'Table'[Qty] ) - _mindayQty, _mindayQty ) )

Put this measure into the table visual, and the result is as follows:

I've provided the PBIX file for this time below, and it would be great if I could help you.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.