Forum Discussion

manojk_pbi's avatar
manojk_pbi
Helper V
2 years ago
Solved

Need help to write DAX or measure

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 

 

MonthYearProductBugs% ChangeThreshold 100
Jan-24Product 1200  
Mar-24Product 11905%8%
Jan-24Product 2100  
Mar-24Product 2125-25%8%
Jan-24Product 3130  
Mar-24Product 311015%8%
Jan-24Product 4160  
Mar-24Product 41553%8%
Jan-24Product 520  
Mar-24Product 523-15%8%
Jan-24Product 640  
Mar-24Product 6385%8%

 

Expected output from above table :

 

 

Thanks,

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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