Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your threadconsider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
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
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).
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
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.
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
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.
No problem for the delay, just interested if my solution was OK, waiting for your answer
Thanks!
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.
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?
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?
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.
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)?
Will fix that in two hours from now and come back
@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
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.
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
@FBergamaschi I don't have permission to upload to your Google Drive, so here's a link to mine.
https://drive.google.com/drive/folders/1KIQxJSgsL3m7YKd3-qf3JyVFpUKoAjPX
the file you shared is my last file
can you please share the file with the sample showing the wrong 8
Thanks
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
74 | |
71 | |
38 | |
29 | |
26 |
User | Count |
---|---|
97 | |
96 | |
60 | |
44 | |
41 |