How to Track Consecutive Monthly Rejections in Power BI with DAX and Reset Counter on "No Rej"

[Sah_123]

Requirements:

1. Rejection Sequence Tracking:

   • For each Material_Code, I need to check if the Rej Qty is greater than 0 for two consecutive months.

   • If Rej Qty > 0 for consecutive months, I need to start with "CS0.1" and increment the sequence for each rejection:

     • First rejection in a consecutive pair: "CS0.1"

     • Second rejection in a consecutive pair: "CS0.2"

     • If the rejection continues in consecutive months, it should be labeled as "CS1", "CS2", etc.

2. Reset on "No Rej":

   • If at any point the Rej Qty = 0 (i.e., "No Rej"), the counter should reset.

   • After a "No Rej", the sequence should start again from "CS0.1" when a rejection occurs in the next month.

3. Example Scenario:

   • April 2025: Rej Qty > 0 → CS0.1

   • May 2025: Rej Qty > 0 → CS0.2 (since it's consecutive)

   • June 2025: Rej Qty > 0 → CS1 (new sequence)

   • July 2025: Rej Qty > 0 → CS2

   • August 2025: Rej Qty = 0 → No Rej

   • September 2025: Rej Qty > 0 → CS0.1 (reset after "No Rej")

   • October 2025: Rej Qty = 0 → No Rej

   • November 2025: Rej Qty > 0 → CS0.1

   • December 2025: Rej Qty > 0 → CS0.2 (consecutive again)

   • January 2026: Rej Qty > 0 → CS1 (new sequence)

What I Need:

I need help creating a DAX formula that can:

1. Track consecutive rejection months (Rej Qty > 0).

2. Start the counter with CS0.1, increment to CS0.2, CS1, CS2, etc.

3. Reset the counter when Rej Qty = 0 and restart with CS0.1 after the "No Rej" period.

4. The formula should handle these rules for each Material_Code.

My Attempt:

I've tried using EARLIER, CALCULATE, and FILTER in DAX, but I am running into issues with handling the reset of the counter after "No Rej". How can I achieve this logic in DAX?
Here is the sample table and output i want

Rej Qty	_Month_Year	_Cs	Fiscal Year	Material_Code	Output1	Output2
10	Apr-21	CS0	2022	86820E_S67	CS0.1	Incident1
4	May-21	CS0	2022	86820E_S67	CS0.2	Incident2
26	Jun-21	CS1	2022	86820E_S67	CS1	CS1
2	Jul-21	CS2	2022	86820E_S67	CS2	CS2
25	Aug-21	CS3	2022	86820E_S67	CS3	Escalation
13	Sep-21	CS4	2022	86820E_S67	CS4	De-rating MSA
0	Oct-21	No Rej	2022	86820E_S67	No rej	No rej
5	Nov-21	CS0	2022	86820E_S67	CS0.1	Incident1
4	Dec-21	CS0	2022	86820E_S67	CS0.2	Incident2
0	Jan-22	No Rej	2022	86820E_S67	No rej	No rej
0	Feb-22	No Rej	2022	86820E_S67	No rej	No rej
3	Mar-22	CS0	2022	86820E_S67	CS0.1	Incident1
0	Apr-22	No Rej	2023	86820E_S67	No rej	No rej

for output2 need 

[v-pagayam-msft]

Hi Sah_123 ,

Could you please confirm if you've resolved the issue? If so, sharing the solution here ,it would be helpful for other community members who may have similar issues.If our answer addressed your query,consider accepting it as solution.

Thank you for being part of the Microsoft Fabric Community.

Regards,
Pallavi G

[v-pagayam-msft]

Hi @Sah_123 ,
Could you please confirm if the issue has been resolved ? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.
Thank you for your understanding and assistance.

[v-pagayam-msft]

Hi @Sah_123 ,
Has the issue been resolved on your end? If so, please share your solution and mark it as "Accept as Solution." This will assist others in the community who are dealing with similar problems and help them find a solution more quickly.

Thank you.

[Sah_123]

Hi, @v-pagayam-msft  thank you for your response, the ouput what i am looking for is different, 

Rej Qty	_Month_Year	_Cs	Fiscal Year	Material_Code	Output1	Output2
10	Apr-21	CS0	2022	86820E_S67	CS0.1	Incident1
4	May-21	CS0	2022	86820E_S67	CS0.2	Incident2
26	Jun-21	CS1	2022	86820E_S67	CS1	CS1
2	Jul-21	CS2	2022	86820E_S67	CS2	CS2
25	Aug-21	CS3	2022	86820E_S67	CS3	Escalation
13	Sep-21	CS4	2022	86820E_S67	CS4	De-rating MSA
0	Oct-21	No Rej	2022	86820E_S67	No rej	No rej
5	Nov-21	CS0	2022	86820E_S67	CS0.1	Incident1
4	Dec-21	CS0	2022	86820E_S67	CS0.2	Incident2
0	Jan-22	No Rej	2022	86820E_S67	No rej	No rej
0	Feb-22	No Rej	2022	86820E_S67	No rej	No rej
3	Mar-22	CS0	2022	86820E_S67	CS0.1	Incident1
0	Apr-22	No Rej	2023	86820E_S67	No rej	No rej

output column not matching with your solution.

[v-pagayam-msft · Accepted solution]

Hi @Sah_123 ,
Thanks for sharing your detailed requirements! Based on your scenario,below are the step-by-step instructions with sample data and DAX queries which might help you to get the desired output.

Please find the below DAX queries:
1.Create a column to sort the months using below:
         MonthSort = FORMAT([_Month_Year], "YYYYMM")

2.Create a Calculated column to look up the previous month's Rej Qty :
    

Prev_Rej_Qty =

VAR CurrentDate = [_Month_Year]

RETURN

CALCULATE(

    MAX('Table'[Rej Qty]),

    FILTER(

        'Table',

        'Table'[Material_Code] = EARLIER('Table'[Material_Code]) &&

        'Table'[_Month_Year] = EDATE(CurrentDate, -1)

    )

)

3.Use the below DAX to get the Output1 – Rejection Sequence
      

Output1 =

VAR Mat = [Material_Code]

VAR ThisDate = [_Month_Year]

VAR ThisGroup = [RejectionGroup]

VAR CurrentRej = [Rej Qty]

-- All rows before this one, same group

VAR History =

    FILTER(

        'Table',

        [Material_Code] = Mat &&

        [RejectionGroup] = ThisGroup &&

        [_Month_Year] < ThisDate &&

        [Rej Qty] > 0

    )

VAR PrevMonth =

    CALCULATE(

        MAX([Rej Qty]),

        FILTER(

            'Table',

            [Material_Code] = Mat &&

            [_Month_Year] = EDATE(ThisDate, -1)

        )

    )

VAR CountInGroup = COUNTROWS(History)

RETURN

SWITCH(

    TRUE(),

    CurrentRej = 0, "No rej",

    CountInGroup = 0, "CS0.1",

    CountInGroup = 1, "CS0.2",

    "CS" & (CountInGroup - 1)

)

4.Use below DAX to get Output2 based on Output1:
     

Output2 =

SWITCH(

    TRUE(),

    [Output1] = "CS0.1", "Incident1",

    [Output1] = "CS0.2", "Incident2",

    [Output1] = "CS1", "CS1",

    [Output1] = "CS2", "CS2",

    [Output1] = "CS3", "Escalation",

    [Output1] = "CS4", "De-rating MSA",

    [Output1] = "No rej", "No rej",

    BLANK()

)

Attaching the screenshot and file for your reference and let us know if you still require any information.


If this answer meets your requirement,consider accepting it as solution.

Regards,
Pallavi.

[v-pagayam-msft]

Hi @Sah_123 ,
Thank you for reaching ou to us.I am happy to help!

Upon my understanding,to correctly track rejection sequences and assign appropriate labels, follow these structured steps:

1.create a calculated column using below:

Date_Column =

DATE(

    2000 + VALUE(RIGHT('Table'[Month_Year], 2)), -- Extract Year

    SWITCH(LEFT('Table'[Month_Year], 3),

        "Jan", 1, "Feb", 2, "Mar", 3, "Apr", 4, "May", 5, "Jun", 6,

        "Jul", 7, "Aug", 8, "Sep", 9, "Oct", 10, "Nov", 11, "Dec", 12

    ),

    1

)

2.Prev rejection

Prev_Rejection =

VAR PrevMonthRej =

    CALCULATE( MAX('Table'[Rej Qty]),

        FILTER('Table',

            'Table'[Material_Code] = EARLIER('Table'[Material_Code]) &&

            'Table'[Date_Column] = EDATE(EARLIER('Table'[Date_Column]), -1)

        )

    )

RETURN

    IF(PrevMonthRej > 0 && 'Table'[Rej Qty] > 0, 1, 0)

 
3.Create a calculated column to flag months with rejections :
 

           Has_Rejection = IF( 'Table'[Rej Qty] > 0, 1, 0 )

4..Create a calculated column to Previous Rejection by using following :

Prev_Rejection =

VAR PrevMonthRej =

    CALCULATE( MAX('Table'[Rej Qty]),

        FILTER('Table',

            'Table'[Material_Code] = EARLIER('Table'[Material_Code]) &&

            'Table'[Date_Column] = EDATE(EARLIER('Table'[Date_Column]), -1)

        )

    )

RETURN

    IF(PrevMonthRej > 0 && 'Table'[Rej Qty] > 0, 1, 0)

4.Now, define the CS_Sequence to track rejection progression:

CS_Sequence =

VAR CurrentMaterial = SELECTEDVALUE('Table'[Material_Code])

VAR CurrentRejQty = SELECTEDVALUE('Table'[Rej Qty])

VAR CurrentDate = SELECTEDVALUE('Table'[Date_Column])

-- Find the previous month's rejection quantity

VAR PrevRejQty =

    CALCULATE(

        MAX('Table'[Rej Qty]),

        FILTER(

            'Table',

            'Table'[Material_Code] = CurrentMaterial &&

            'Table'[Date_Column] = EDATE(CurrentDate, -1)

        )

    )

-- Find the previous sequence number

VAR PrevCS_Num =

    CALCULATE(

        MAXX(

            FILTER(

                'Table',

                'Table'[Material_Code] = CurrentMaterial &&

                'Table'[Date_Column] = EDATE(CurrentDate, -1)

            ),

            IF('Table'[Rej Qty] > 0, 1, 0)  -- Ensure we're checking valid rejection values

        )

    )

-- Determine the rejection sequence logic

RETURN

    IF(CurrentRejQty = 0, "No Rej",

        IF(ISBLANK(PrevRejQty) || PrevRejQty = 0, "CS0.1",

            IF(PrevCS_Num = 1, "CS0.2",

                "CS" & (PrevCS_Num + 1)

            )

        )

    )

5. Create a column to classify rejection sequences

Output_Label =

SWITCH( TRUE(),

    'Table'[CS_Sequence] = "CS0.1", "Incident1",

    'Table'[CS_Sequence] = "CS0.2", "Incident2",

    'Table'[CS_Sequence] = "CS1", "CS1",

    'Table'[CS_Sequence] = "CS2", "CS2",

    'Table'[CS_Sequence] = "CS3", "Escalation",

    'Table'[CS_Sequence] = "CS4", "De-rating MSA",

    "No Rej"

)

 
6.Now create a Table Visual in Power BI.Attaching the file for your reference.

If this solution works for you, please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Regards,
Pallavi G.