Need help to write DAX or measure

[manojk_pbi]

Hi Friends,

I am working on some data to represent the trends of % Change in Bugs. The change is always calculated for the current month & baseline will Janunary (opening counts). The baseline can be flexible sometime it can go to the previous years month. How can we write a DAX or measure where baseline month can be configure and % change can be calculated using the current data.

Please let me know if there are any suggesstions? It will be of great help.

For better understanding i am adding sample data and expected graph below

% Change formula = (Baseline Month Data - Current Month Data)/Baseline Month Data

Threshold Static - Expected % change 

MonthYear	Product	Bugs	% Change	Threshold 100
Jan-24	Product 1	200
Mar-24	Product 1	190	5%	8%
Jan-24	Product 2	100
Mar-24	Product 2	125	-25%	8%
Jan-24	Product 3	130
Mar-24	Product 3	110	15%	8%
Jan-24	Product 4	160
Mar-24	Product 4	155	3%	8%
Jan-24	Product 5	20
Mar-24	Product 5	23	-15%	8%
Jan-24	Product 6	40
Mar-24	Product 6	38	5%	8%

Expected output from above table :

Thanks,

[Anonymous · Accepted solution]

HI @manojk_pbi,

You can try to use the following measure formula if it suitable for your requirement:

% Change formula =
VAR baseData = 'formula that calculate the baseline data'
VAR currData =
    CALCULATE (
        SUM ( Table1[Bugs] ),
        ALLSELECTED ( Table1 ),
        VALUES ( Table1[MonthYear] ),
        VALUES ( Table1[Product] )
    )
RETURN
    DIVIDE ( baseData - currData, baseData, -1 )

Notice: replace the baseData variable part with your calculation to get the initzation data to calculate.

Regards,

Xiaoxin Sheng