Solved: Re: Count the number of trailing consecutive month...

lemoncheesecake · ‎07-16-2025

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 @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(

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

View solution in original post

FBergamaschi · ‎07-16-2025

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 · ‎07-17-2025

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 · ‎07-17-2025

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 · ‎07-17-2025

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

lemoncheesecake

Hi @FBergamaschi

Sorry for the delayed response. Yes, you're right in the logic that if there is no data in, say, June 2025, then the trailing months should stop at the latest month with data (e.g., May 2025). I'll take a look at the measure in your separate post and see if it works when applied to my full data set.

I'm glad it was a challenge for you.

FBergamaschi

No problem for the delay, just interested if my solution was OK, waiting for your answer

Thanks!

v-tejrama

Hi @lemoncheesecake,

Thank you @FBergamaschi for the answer provided.
I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.

FBergamaschi · ‎07-17-2025

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?

lemoncheesecake

Hi @FBergamaschi,

My apologies for the late response to this question. I just responded to the response from v-tejrama addressing this question.

Below is additional data that shows the expected logic.

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
Camden	1/11/2024	10	13.88
Camden	1/12/2024	8	13.88
Camden	1/01/2025	4	13.88
Camden	1/02/2025	2	13.88
Camden	1/03/2025	30	13.88
Camden	1/04/2025	45	13.88
Camden	1/05/2025	2	13.88
Camden	1/06/2025	10	13.88

The expected answers are as follows:

Region		Solution
Leiceister		0
Camden		2

Perhaps I should have specified that the relative month where the streak should begin should always be the last available month of data for each specific region.

FBergamaschi

Hi,

so the group of consecutive months should only be considered if this group includes the last available month for each region (and that month is below treshold)?

lemoncheesecake

@FBergamaschi Yes, that is correct.

FBergamaschi

Will fix that in two hours from now and come back

FBergamaschi

@lemoncheesecake done

The updated pbix is available here

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

can you confirm it is now ok or not?

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

I tried applying this solution to the new sample data with Camden and Leiceister, and I can't for the life of me work out why Camden is showing up as 8.

FBergamaschi

No problem, just share the file with me, can you? You can copy that with a different name in the folder I shared, let me know when done and I shall fix that

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