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

[GirSah]

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	4086820E_S67260	CS0.1	Incident1
4	May-21	CS0	2022	4086820E_S67260	CS0.2	Incident2
26	Jun-21	CS1	2022	4086820E_S67260	CS1	CS1
2	Jul-21	CS2	2022	4086820E_S67260	CS2	CS2
25	Aug-21	CS3	2022	4086820E_S67260	CS3	Escalation
13	Sep-21	CS4	2022	4086820E_S67260	CS4	De-rating MSA
0	Oct-21	No Rej	2022	4086820E_S67260	No rej	No rej
5	Nov-21	CS0	2022	4086820E_S67260	CS0.1	Incident1
4	Dec-21	CS0	2022	4086820E_S67260	CS0.2	Incident2
0	Jan-22	No Rej	2022	4086820E_S67260	No rej	No rej
0	Feb-22	No Rej	2022	4086820E_S67260	No rej	No rej
3	Mar-22	CS0	2022	4086820E_S67260	CS0.1	Incident1
0	Apr-22	No Rej	2023	4086820E_S67260	No rej	No rej

[v-pgoloju]

Hi @GirSah ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[V-yubandi-msft]

Hi @GirSah ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[V-yubandi-msft]

Hi @GirSah ,
We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.

Your feedback is valuable to us, and we look forward to hearing from you soon.

Thanks..

[V-yubandi-msft]

Hi @GirSah ,

Thank you for reaching out to the Microsoft Fabric community.

create a Index column first with the help of power query then create calculated column .

Output1 =

VAR CurrentMonth = 'rej'[Month_Year]

VAR CurrentRejQty = 'rej'[Rej Qty]



-- Get the previous month's row for the same Material_Code

VAR PreviousRow =

    CALCULATE(

        MAX('rej'[Index]),

        FILTER(

            'rej',

            'rej'[Index] < EARLIER('rej'[Index]) &&

            'rej'[Material_Code] = EARLIER('rej'[Material_Code])

        )

    )



-- Get the rejection quantity of the previous row

VAR PrevRejQty =

    LOOKUPVALUE(

        'rej'[Rej Qty],

        'rej'[Index], PreviousRow

    )



-- Get the previous sequence value directly from a derived logic

VAR PreviousSequence =

    IF(

        PrevRejQty = 0,

        "No Rej",

        IF(

            PrevRejQty > 0,

            "CS0.1" -- Initialize the sequence for simplicity

        )

    )



-- Define whether rejection is consecutive

VAR IsConsecutiveRej = CurrentRejQty > 0 && PrevRejQty > 0

VAR IsReset = CurrentRejQty = 0



-- Generate the new output sequence dynamically without relying on `Output1` column

VAR NewOutput =

    IF(

        IsReset,

        "No Rej",

        IF(

            IsConsecutiveRej,

            IF(

                LEFT(PreviousSequence, 3) = "CS0",

                "CS0." & VALUE(MID(PreviousSequence, 5, LEN(PreviousSequence))) + 1,

                "CS1"

            ),

            "CS0.1"

        )

    )

RETURN

    NewOutput

If my response solved your query, please mark it as the Accepted solution to help others find it easily.

And if my answer was helpful, I'd really appreciate a 'Kudos'.