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

[FBergamaschi]

Hi,

it was tough I have to say. That is nice 🙂

Is this the result you are looking for ? I ask because the values for Croydon is 2 to me (only may and june) but I see 5 in your teable fo expected result

IN case this is ok i shall explain how to do this

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]

I'm glad it was a suitable challenge 😊

Croydon should be 5, because with an average monthly sales of 18.75 (150 / 8 months), February, May, and June are under the threshold, so the formula should pick up February (the earliest consecutive month under the threshold incl. months with no sales) to June (the latest month with sales data).

[FBergamaschi]

This complicates a bit the calculation because those months are not in the fact table

So my suggestion is to insert one row with Sales = 0 e the result will automatically be correct (you need to insert march and aprile for Croydon with zero Zales, so it is a sales below 20 and below the average of the month so will automatically be considered. I did that and this is the result (if you want I can reshare the file)

Otherwise the complexity increases a lot, I can of course try to solve it anyway but if this is a solution meanwhile... 

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]

Thanks for the response.

Unfortunately the data is coming directly from an SAP Message Server so I can't directly modify the data to add lines. It might be possible to add lines using a formula, but that would require me to know when a region started getting sales or had its last month of sales to ensure I don't add a month that legitimately should be blank.

I also considered a formula that brings up a TRUE FALSE flag based on the criteria and then a second column that just sums up the column where I have the variances between months, which would ensure those missing months are taken into account.

But all these factors do make this a difficult problem to solve.

[FBergamaschi]

@lemoncheesecake 

A promise is a promise, so here the solution all in DAX without any rows to add, so you have a mixed  power query / DAX solution form another solver and this one completely in DAX from me, please consider them both as potential solutions and for kudos 🙂

Result now in line with your request

1 - Notes

Note1 - There may be multiple consecutive month groups to consider; I took the maximum number of consecutive months among all consecutive month groups in a region. In the data you sent this does not happen, but better safe than sorry. If you want a different approach, please let me know

Note2 - at the moment, with no sales in a region for several months, until I have sales again, I don't consider those months (remember the Croydon thing: suppose the data for may (and june of course) is not there as we are at the end of april and I only have feb as last month for that region, on which base I need to consider march?). To solve this you might consider taking, as maximum month to consider for all the regions, today's month or the month of maximum sales (removing the filter from the region), also here let me know if you need help to do the last fixes

The above are very important points to consider that I thik were not outlined before by anyone and therefore might not have been put into action with a solution

2 - Solution steps

Step1 - create a date tabel (any data model should have one, I created it in DAX, it automatically updates)

Step2 - create a Region Table (as aboe I created it in DAX, it automatically updates)

Step3 - create a data model Sales, Date, Region

Step4 - create a simple calculate column in Sales to lable months in line with your criteria (Sales less than 20 and less then average sales for that region) and another simple one to turn months in to nrs and simplify the calculations

Step 5 - create a (challenging) measure that I will show to the community in a separate post

The pbix is available here

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

Please @me in your answer

and thank again, it was really tought but very pleasant

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

[FBergamaschi]

@lemoncheesecake

Can you please give a feedback on the solutions offered by me and others?

Thanks

[FBergamaschi]

I will solve all in DAX so you have multiple optional

One question: what if there are multiple groups of consecutive months? What do you want to see in that case?

[FBergamaschi]

My question is for @lemoncheesecake 

I will solve all in DAX so you have multiple optional

One question: what if there are multiple groups of consecutive months? What do you want to see in that case?

[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.

[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.