Count the number of trailing consecutive months based on multiple criteria

[lemoncheesecake]

Hi,

I am working on a dashboard where I have to count the number of the latest consecutive months by region that meet mulitple criteria, which in this case is 1) the monthly sales is lower than the average monthly sales for the region, and 2) the monthly sales is less than or equal to 20. There is also a peculiarity in the data where when there is no sales data, there is a gap in time like in this example for Croydon between February and May. I was thinking of addressing this gap by adding a column which is the number of months between the previous row and the current row if they share the same region, and my measure would be a SUMIFS.

The consecutive trailing months would be for instance in Bexley, February 2025 is beneath the criteria threshold, but is not consecutive with the latest month in the data because March 2025 is above the threshold, thus it would only count April 2025 to June 2025 (three months).

However, I do not know how write in DAX any formula that can count the latest consecutive months based on multiple criteria. The expected output for the below snippet of data would be like the second table. Could anyone please offer a hand on creating a functional formula?

Thank you very much in advance! 

Region	Month		Sales (GBP)	Average Monthly Sales (GBP)	Variance in Months
Bromley	1/11/2024		24	18.33	1
Bromley	1/12/2024		30	18.33	1
Bromley	1/01/2025		13	18.33	1
Bromley	1/02/2025		13	18.33	1
Bromley	1/03/2025		17	18.33	1
Bromley	1/04/2025		13	18.33	1
Bexley	1/09/2024		11	30.50	1
Bexley	1/10/2024		24	30.50	1
Bexley	1/11/2024		62	30.50	1
Bexley	1/12/2024		63	30.50	1
Bexley	1/01/2025		54	30.50	1
Bexley	1/02/2025		16	30.50	1
Bexley	1/03/2025		35	30.50	1
Bexley	1/04/2025		12	30.50	1
Bexley	1/05/2025		13	30.50	1
Bexley	1/06/2025		15	30.50	1
Croydon	1/09/2024		20	18.75	1
Croydon	1/10/2024		15	18.75	1
Croydon	1/11/2024		35	18.75	1
Croydon	1/12/2024		24	18.75	1
Croydon	1/01/2025		23	18.75	1
Croydon	1/02/2025		9	18.75	1
Croydon	1/05/2025		10	18.75	3
Croydon	1/06/2025		14	18.75	1

[lemoncheesecake]

The file I uploaded has the updated data set. 

[FBergamaschi]

unfortunately I do not see those numbers, I am not sure why

Let's do like this: I send you my mail via private message and you send me the pbix there ok? A private message is coming

[FBergamaschi]

Now I have this result

is that ok? 

File is again at my googledrive

https://drive.google.com/drive/folders/1KIQxJSgsL3m7YKd3-qf3JyVFpUKoAjPX?usp=sharing

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[lemoncheesecake]

@FBergamaschi  Yes, that's perfect. I get the same result and I did a larger spot check with my population data, and they match what I would get with manual calculation.

Thank you for your help!

If you are going to post again with an explanation of your solution for everyone else, then I'll mark that as the solution.

Thanks again.

[FBergamaschi · Accepted solution]

Hi @lemoncheesecake ,

what I can do is post the DAX here and let anyone that wants an explanation to ask it and I shall answer. Point is that it is a very complex DAX

I hope you can mark it as a solution anyway.

here is the DAX of the measure

MaxConsMonths =

VAR MinumumMonth = CALCULATE( MIN ( Sales[YearMonthNr] ), REMOVEFILTERS( 'Date' ) )

VAR MaximumMonth = CALCULATE( MAX ( Sales[YearMonthNr] ), REMOVEFILTERS( 'Date' ) )

VAR Months =

SELECTCOLUMNS(

FILTER (

    ADDCOLUMNS(

    ADDCOLUMNS(

        CROSSJOIN( ALL ( 'Date'[YearMonthNr], 'Date'[YearMonth] ), VALUES ( Region[Region] ) ),

        "@Sales", CALCULATE( SUM ( Sales[Sales (GBP)] ) )

    ),

     "@ToCons", IF ( [@Sales] > 0, CALCULATE( SELECTEDVALUE( Sales[To Consider] , 0 ) ), 1 )

    ),

    [@ToCons] = 1 &&

    'Date'[YearMonthNr] >=MinumumMonth && 'Date'[YearMonthNr] <=MaximumMonth

),

'Date'[YearMonth], 'Date'[YearMonthNr], Region[Region]

)

VAR ConsecutiveMonths =

ADDCOLUMNS(

    Months,

    "@ConsecMonth",

    VAR Region = Region[Region]

    VAR RefMonth = [YearMonthNr]

    VAR PrevMonth =

    MAXX ( FILTER ( Months, [YearMonthNr] < RefMonth ), [YearMonthNr] )

    VAR _NextMonth =

    MINX ( FILTER ( Months, [YearMonthNr] > RefMonth ), [YearMonthNr] )

    VAR Result = IF ( PrevMonth = RefMonth-1 || _NextMonth = RefMonth+1, 1, 0  )

    RETURN

    Result,

    "@ConsFromCalendar",

    VAR Region = Region[Region]

    VAR RefMonth = [YearMonthNr]

    VAR PrevMonth =

    MAXX ( FILTER ( Months, [YearMonthNr] < RefMonth ), [YearMonthNr] )

    VAR PrevMonthCal =

    MAXX ( FILTER ( ALL ( 'Date'[YearMonthNr] ), [YearMonthNr] < RefMonth ), [YearMonthNr] )

    VAR _NextMonth =

    MINX ( FILTER ( Months, [YearMonthNr] > RefMonth ), [YearMonthNr] )

    VAR _NextMonthCal =

    MINX ( FILTER ( ALL ( 'Date'[YearMonthNr] ), [YearMonthNr] > RefMonth ), [YearMonthNr] )

    VAR Result = IF ( PrevMonth = PrevMonthCal, 1, 0  )

    RETURN

    Result

)

VAR NumberConsecutiveMonths =

ADDCOLUMNS(

    ConsecutiveMonths,

    "@NrConsMonths",

    VAR RefMonth = [YearMonthNr]

    VAR First = MAXX ( FILTER ( ConsecutiveMonths, [YearMonthNr]<=RefMonth && [@ConsecMonth] = 1 && [@ConsFromCalendar] = 0 ), [YearMonthNr])

    RETURN IF ( [@ConsecMonth] = 1, RefMonth-First+1 )

)

VAR MaxMOnthNonConsCal = MAXX ( FILTER ( NumberConsecutiveMonths, [@ConsFromCalendar] = 0 ), 'Date'[YearMonthNr] )

VAR MaxNrConsMonths = MAXX( FILTER ( NumberConsecutiveMonths, 'Date'[YearMonthNr]>=MaxMOnthNonConsCal), [@NrConsMonths] )

VAR MonthsIncludedInMaxNrConsMonths =

SELECTCOLUMNS(

    FILTER ( NumberConsecutiveMonths, [@NrConsMonths] = MaxNrConsMonths ),

    'Date'[YearMonthNr]

)

RETURN IF ( MaximumMonth IN MonthsIncludedInMaxNrConsMonths, MaxNrConsMonths)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[v-tejrama]

Hi @FBergamaschi ,

Thanks for confirming!

Since you asked for a more specific implementation, here’s a starting DAX formula to identify qualified months:

IsQualifiedMonth =
VAR RegionAvg = CALCULATE(AVERAGE('Sales'[MonthlySales]), ALLEXCEPT('Sales', 'Sales'[Region]))
RETURN IF('Sales'[MonthlySales] <= 20 && 'Sales'[MonthlySales] < RegionAvg, 1, 0)

Then in Power Query, sort your table by Region and Month, and create an Index column (starting at 1 per Region). Use DAX with EDATE logic to group streaks where qualified months are consecutive. Finally, summarize to get the max streak per Region.

Thank you again.

[v-tejrama]

Hi @lemoncheesecake ,

Thanks for reaching out to the Microsoft fabric community forum.

Here's a clear approach to identifying the latest consecutive months for each region where monthly sales are (1) less than or equal to 20, and (2) also below the region’s average, ensuring no missing months in between. This can be achieved entirely in Power BI using DAX, without inserting dummy rows.

Start by adding a calculated column, IsQualifiedMonth, which returns 1 if a month’s sales are less than or equal to 20 and below the region’s average (use a VAR to store the average for comparison). In Power Query, add an Index column that starts from 1 per region, sorted by month.

Next, create a DAX column to group consecutive qualifying months by checking if the current month is one month after the previous (using EDATE) and both are qualified. This allows you to assign a group ID to each streak.

Summarize by region and group ID to count the months in each streak, then create another summary table to find the maximum streak length for each region. The result will give you the longest run of consecutive qualifying months per region, matching the example output such as 3 for Bexley, 4 for Bromley, and 5 for Croydon.

If you need the specific DAX formulas or a sample PBIX file, let me know.

Thank you,

Tejaswi.

[lemoncheesecake]

@v-tejrama Hi Tejaswi,

I would greatly appreciate a sample PBIX file or the specific formulae for this solution. I am not fully familiar with storing VAR. One thing I noticed about both your solution and @FBergamaschi's solution is that it calculates the maximum streak, but my problem requires the size of the latest streak at the end of the available data for each region. For example, if we had a fourth region, Leiceister with the following data set (below), the solution would show 0 for this region, because while there are months below the threshold, the latest streak is above the threshold.

Region		Month		Sales	Average
Leiceister		1/11/2024		15	21.63
Leiceister		1/12/2024		12	21.63
Leiceister		1/01/2025		16	21.63
Leiceister		1/02/2025		29	21.63
Leiceister		1/03/2025		20	21.63
Leiceister		1/04/2025		23	21.63
Leiceister		1/05/2025		32	21.63
Leiceister		1/06/2025		26	21.63

[v-tejrama]

Hi again @lemoncheesecake ,

Thanks for the follow up,

You can easily achieve this in Power BI. Start by loading your data with the columns: Region, Month (as Date), Sales, and Average.

Next, create a calculated column:

IsQualifiedMonth = IF(Sales[Sales] <= 20 && Sales[Sales] < Sales[Average], 1, 0)

In Power Query, sort your data by Region and Month in ascending order, then add an index column for each Region (such as IndexInRegion). Once you apply these changes, define a DAX measure to determine the latest qualified streak—check if the most recent month qualifies, and count how many consecutive months (going backwards) also qualify. Add both Region and this measure to a table visual. This approach will deliver the latest streak for each Region, not the maximum streak, giving you exactly the insight you need.

Please find the attached PBIX and Screenshort file for your reference.

Best Regards,
Tejaswi.

[Greg_Deckler]

@lemoncheesecake It would be some variation of Cthulhu. Cthulhu - Microsoft Fabric Community The latest iteration of Cthulhu is in my newest book (below). If I get a chance I will try to adapt it to your criteria. That said, I'm not sure I understand the criteria fully. It looks like Bexley is always above 20 so it shouldn't have any and be zero?

[lemoncheesecake]

Sorry, it looks the formatting of the data I copied over doesn't look too great. The Sales looks like it's concatenated with the date. For Bexley, in June 2025, the monthly sales is 15, which is lower than the threshold of the average and the 20 absolute days.