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

[manojk_pbi]

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

[v-jianpeng-msft · Accepted solution]

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.

[v-jianpeng-msft]

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

[bhanu_gautam]

Try creating measure and pull that into visuals

[bhanu_gautam]

@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

[manojk_pbi]

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