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,

[manojk_pbi]

Hi @Anonymous ,

Thanks for you suggestion.

Since I am new to DAX queries, I couldn't understand the measure written to create a column. Is it assumed to be 2 values per product and then compared or can it work for N data points. 

Please can you elaborate bit more on this works 

[Anonymous]

Hi, @manojk_pbi 

Select New A Column on the modeling page

Or right-click on your table to create a column instead of selecting a metric. These two are not quite the same

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[manojk_pbi]

This steps is clear. I didn't understand the logic of Change column how it works ? Is it fixed for 2 rows or can be used for N records

[Anonymous]

Hi, @manojk_pbi 

It can be used for N records.

You can search for documentation on the relationship between Measure and Calculated column, such as the link below:

Calculated Columns and Measures in DAX - SQLBI

Best Regards

Yongkang Hua

[manojk_pbi]

Thanks for your suggestions. I understood the logic now. 

As per logic, always the difference is calculated with current & previous record. This is once of the scenario, actually it should calculate change w.r.t to Jan'24 or Start of the Year or even better if it can be configured. 

Please could you help me with modified DAX for the same. 

Thanks in advance.

[Anonymous · Accepted solution]

Hi, @manojk_pbi 

Custom Column Index in PowerQuery 

Then create new columns

Change =
VAR _perviousIndex =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Index] < EARLIER ( 'Table'[Index] )
                && 'Table'[Product] = EARLIER ( 'Table'[Product] )
        )
    )
VAR _perviousBugs =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[Bugs] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = _perviousIndex )
    )
RETURN
    IF (
        _perviousBugs <> BLANK (),
        ( _perviousBugs - 'Table'[Bugs] ) / _perviousBugs
    )

Threshold = IF ( [MonthYear] = DATE(2024,3,24), 0.08, BLANK() )

Here is my preview:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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