Is it possible to group months together in a custom way using DAX in Power BI?

[arpost]

I have a scenario where I need to create groups for over 1 million records as follows:

1. 0-6 Months
2. 7-12 Months
3. 13-24 Months
4. 25+ Months

These will be used to show how a running total for where a specific customer was at the range of time mentioned. Here's a sample of what the data would look like:

Customer	Date
Acme Co.	1/1/21
Acme Co.	1/1/21
Acme Co.	12/1/22
Acme Co.	1/2/23
Acme Co.	2/2/23
Acme Co.	3/3/23
Acme Co.	5/2/23
Acme Co.	7/18/23
RoadTech	1/1/21
RoadTech	1/1/21
RoadTech	8/1/22

These groups will ultimately apply to specific customers in a list like so:

Customer	Date	MonthSequence
Acme Co.	1/1/21	1
Acme Co.	1/1/21	1
Acme Co.	12/1/22	2
Acme Co.	1/2/23	3
Acme Co.	2/2/23	4
Acme Co.	3/3/23	5
Acme Co.	5/2/23	6
Acme Co.	7/18/23	7
RoadTech	1/1/21	1
RoadTech	1/1/21	1
RoadTech	8/1/22	2

In case it helps, here's a quick explanation behind the "logic" guiding this need:

Let's say a customer bought 10 items in January, nothing in February, and then 1 item in March, and then nothing until July. I want all items in January to be marked as belonging to the 1st "month" of purchases, the item in March to be marked as the 2nd "month," items in July marked as the 3rd "month" and so on. This numbering system needs to restart with each customer.

Can the MonthSequence column be created in DAX? I'm hoping to avoid writing data to the model if I can help it for performance, but I'm not opposed to it (see related post but from a Power Query perspective here).

[arpost]

@amitchandak, any brilliant insights on this one? 😊

[amitchandak · Accepted solution]

@arpost , I think the last solution should be fine once you have the month sequence. 

For that, I think you can use rank, that too on year month 

Year Month = format([Date], "YYYYMM")  // Or // Year([Date])*100 + Month([Date]) 

You can have that as var, but might go well with rank 

MonthSequence = Rankx(

FILTER('Table','Table'[Customer]=EARLIER('Table'[Customer])), Table[Year Month], ,asc,dense) 

Hope I got it right

[ebeery]

@arpost I think the performance impact of adding one column such as you've described to a dataset with ≈1 million records is going to be negligible.

I would just add a conditional column in Power Query, something like below.  You can use the built-in menu or write from scratch in M.

Alternatively you could write in DAX, yielding equivalent result:

Period_DAX = 
SWITCH(
    TRUE(),
    'Table'[MonthSequence] >= 25, "25+ Months",
    'Table'[MonthSequence] >= 13, "13-24 Months",
    'Table'[MonthSequence] >= 7,  "7-12 Months",
    'Table'[MonthSequence] >= 0, "0-6 Months"
)

[arpost]

@ebeery, thanks for responding and my apologies! I realized my original post wasn't clear.

My problem is more with figuring out how to generate the MonthSequence piece. I was hoping there was some creative way that I could group months together so that (1) records in the same month get grouped together and (2) the months are then grouped in accordance with the periods shown. Kind of like how in Excel a person can group a list of days from 1 to 30 into bins of 1-10, 11-21, 22-30.

I updated the original post.

[ebeery]

@arpost can you elaborate on what "MonthSequence" actually is intended to represent?  It's still not at all clear to me from the info you've provided.

[arpost]

@ebeery , certainly, and apologies. Basically, I am wanting to group together records with dates from the date a client started with a company. These records may occur only once every few months, but I want to treat these months as part of a sequence.

So, let's say a customer bought 10 items in January, nothing in February, and then 10 more items in March. I want all items in January to be marked as belonging to the 1st "month" of purchases, the items in March be marked as the 2nd "month," items in July marked as the 3rd "month" and so on. This numbering system needs to restart with each customer.

This will then be used to help a finance team track the client's spend compared to a forecast that estimated we'd hit a goal by a certain period (e.g., 7 months after a client starts with us, we project we'll profit $5000).

Does that help?

[arpost]

I just threw this together in Excel to illustrate the kind of logic I'd use there for the MonthSequence in case this helps. I need to replicate this behavior in Power BI but am unsure how to do so.