Custom Column to Check if 2 consecutive months met a condition

[beanboy]

Hi Everyone,

I have a Customers table, and am looking for a way to add a flag column that flags if the Customer had 2 consecutive months of Calls that are greater than a target. The 'consecutive months' part is what is tripping me up.

I can do this the 'wrong' way of creating 12 variables, one for each Month, and doing a big series of checks:

IF ( M1 > Target && M2 > Target || M2 > Target && M3 > Target || etc..., "Flag" ) but I figure there's a better way.

I asked Copilot and got started, but again, I'm getting stuck on the consecutive months part of this. 
First, for each row in the Customers table I am creating a temp table in memory that is storing the monthly Call volume for the trailing 12 months. 

VAR __MonthlyTarget = 'Customers'[3 Month Target] / 3

VAR __EndDate = EOMONTH ( TODAY(), -1 )
VAR __StartDate = EOMONTH ( TODAY(), -13 )

VAR __Months = 
    SUMMARIZE (
        FILTER ( 
            'Calendar', 
            'Calendar'[Date] >= __StartDate && 'Calendar'[Date] <= __EndDate
        ),
        'Calendar'[Month_Year],
        "Calls", 
            [#Calls]
    )

 
Now, I need to check if this __Months temp table has 2 consecutive rows of Calls > __MonthlyTarget.

Copilot is telling me something along the lines of the below code.

VAR __ConsecMonthsExceed =
    SUMX (
        FILTER ( 
            __Months,
            [Calls] > __MonthlyTarget &&
            CALCULATE ( 
                SUM ( [Calls] ),
                FILTER ( 
                    __Months,
                    [Month_Year] = EARLIER ( [Month_Year] ) +1
                ) 
            ) > __MonthlyTarget
        ),
        1
    )



RETURN 
IF ( __ConsecMonthExceeded > 0, "Flag" )

 
The Month_Year column is a date value (i.e. 1/1/2025, 2/1/2025, etc) so yes I am aware doing [Month_Year] + 1 probably isn't going up a whole month, but actually a day. This needs to be cleaned up, but isn't what I am stuck on. Im stuck on the 2nd check of the next month's Calls. Thanks for the help. 

[v-kathullac]

Hi @beanboy,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithanya.

[v-kathullac]

Hi @beanboy,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithanya.

[v-kathullac]

Hi @beanboy,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithanya.

[Anonymous]

Hi @beanboy,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[techies]

Hi @beanboy have done this using calculated columns, please try this

Calls Per Month =

VAR YearMonthKey = FORMAT(Calls[Call Date], "YYYYMM")  

RETURN

    CALCULATE(

        COUNT(Calls[Call ID]),  

        FILTER(

            Calls,

            Calls[Customer ID] = EARLIER(Calls[Customer ID]) &&

            FORMAT(Calls[Call Date], "YYYYMM") = YearMonthKey  

        )

    )

Exceeded Target =

VAR Target = LOOKUPVALUE(Customers[Month Target], Customers[Customer ID], Calls[Customer ID])

RETURN IF(Calls[Calls Per Month] > Target, 1, 0)

Consecutive Exceeded Target =

VAR PreviousMonthExceeded =

    CALCULATE(

        MAX(Calls[Exceeded Target]),  

        FILTER(

            Calls,

            Calls[Customer ID] = EARLIER(Calls[Customer ID]) &&  

            (

                (Calls[Year] = EARLIER(Calls[Year]) && Calls[Month] = EARLIER(Calls[Month]) - 1) ||  

                (Calls[Year] = EARLIER(Calls[Year]) - 1 && EARLIER(Calls[Month]) = 1 && Calls[Month] = 12)

            )

        )

    )

RETURN

IF(Calls[Exceeded Target] = 1 && PreviousMonthExceeded = 1, 1, 0)

[Greg_Deckler]

@beanboy Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[beanboy]

Thanks for responding. 

Lets say I have a Customers Table like the following:

Customer ID	Customer Name	Month Target	2 Consecutive Months Flag
C001	Cust1	2
C002	Cust2	3

Customers table is related to the Calls fact table on the CustomerID field. There is one row in the table for each Call, and there can be multiple calls for a Customer on the same day

Call ID	Call Date	Customer ID
1001	1/1/2025	C001
1002	1/1/2025	C001
1003	1/1/2025	C001
1004	1/1/2025	C002
1005	1/2/2025	C001
1006	1/2/2025	C002
1007	2/1/2025	C001
1008	2/2/2025	C001
1009	2/2/2025	C001

I have an existing measure to count calls:

#Calls = COUNTROWS ( Calls )

Currently it is March 26th, so the trailing 12 months is Mar 2024 - Feb 2025. 

I am looking to flag for each Customer if they had 2 consecutive months between Mar 2024 - Feb 2025 where the [#Calls] was greater than the Target.

In the example data above, Customer C001 had 4 calls in Jan2025 and 3 in Feb2025, and their target for each month was 2, so C001 did have 2 consecutive months exceeding the target. 

[Anonymous]

Hi @beanboy,

Thank you for reaching out to Microsoft Fabric Community Forum.

I have taken sample data to reproduce your issue. Please test this query.

Call Month = MONTH(Calls[Call Date])
Call Year = YEAR(Calls[Call Date])
 
Two Consecutive Months Flag =
VAR CallsByMonth =
    ADDCOLUMNS(
        SUMMARIZE(
            Calls,
            Calls[Customer ID],
            Calls[Call Year],
            Calls[Call Month]
        ),
       "Calls Count", COUNTROWS(Calls),
       "Target", LOOKUPVALUE(Customers[Month Target], Customers[Customer ID], Calls[Customer ID])
    )
 
VAR ConsecutiveCheck =
    SUMX(
        CallsByMonth,
        VAR CurrentYear = Calls[Call Year]
        VAR CurrentMonth = Calls[Call Month]
        VAR CurrentMonthCalls = [Calls Count]
        VAR Target = [Target]
       
        VAR NextMonthCalls =
            CALCULATE(
                SUMX(CallsByMonth, [Calls Count]),
                FILTER(
                    CallsByMonth,
                    Calls[Customer ID] = EARLIER(Calls[Customer ID]) &&
                    Calls[Call Year] = CurrentYear &&
                    Calls[Call Month] = CurrentMonth + 1
                )
            )
 
        RETURN
            IF(CurrentMonthCalls > Target && NextMonthCalls > Target, 1, 0)
    )
 
RETURN
    IF(ConsecutiveCheck > 0, "Yes", "No")

Here is the output:

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu