cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Semih87
Helper I
Helper I

Segment movement between two dates

Dear all,

 

I would like to calculate the segment movement of customers between two dates, but I'dont have any idea how to model or wich dax formula to use. I have following table:

Table.png

 

The goal is to get a cross table by using following slicers:

slicers.png

 

 

Cross Table.png

 

Let me explain on two customers how the desired cross table should be calculated.

Customer 1:

 - Classified as A in 2018

 - Classified as B in 2019

Customer 3:

 - Doesn't exist in 2018

 - Classified as B in 2019

Calculation.png

 

My question to you: How can I realize that? Is there already a DAX pattern (I googled it but unfortunately I couldn't find anything)?

Thank you in advance.

 

Best regards

Semih

1 ACCEPTED SOLUTION
sturlaws
Resident Rockstar
Resident Rockstar

Hi @Semih87,

 

I can think of a few possibilities on how to solve this, although some of them will only work if your dataset is fairly small.

 

I have created a report based on your sample data and requirements: report, using one of the possibilities. This one does not involve any tampering with the table, but it needs 4 extra tables/dimensions, all with one single column: year_to, year_from, classification_to and classification_from. The year-tables must contain all years from the table, while the classification-tables must contain all the segments. In addition, the classification_to-table needs a row with the value "Lost", and the classification_from-table needs "New". These 4 tables connects to the main table with 1:many-relationships. 

After that everything is handled by a measure. The DAX-code is a bit lengthy, but it is not very complicated DAX, so don't let it scare you.

I have only tested it on the data you provided, you should probably test it on a larger dataset.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too. 

 

 

https://www.dropbox.com/s/olq93ij5y3tgzjz/Segment%20movement%20between%20two%20dates.pbix?dl=0

View solution in original post

3 REPLIES 3
sturlaws
Resident Rockstar
Resident Rockstar

Hi @Semih87,

 

I can think of a few possibilities on how to solve this, although some of them will only work if your dataset is fairly small.

 

I have created a report based on your sample data and requirements: report, using one of the possibilities. This one does not involve any tampering with the table, but it needs 4 extra tables/dimensions, all with one single column: year_to, year_from, classification_to and classification_from. The year-tables must contain all years from the table, while the classification-tables must contain all the segments. In addition, the classification_to-table needs a row with the value "Lost", and the classification_from-table needs "New". These 4 tables connects to the main table with 1:many-relationships. 

After that everything is handled by a measure. The DAX-code is a bit lengthy, but it is not very complicated DAX, so don't let it scare you.

I have only tested it on the data you provided, you should probably test it on a larger dataset.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too. 

 

 

https://www.dropbox.com/s/olq93ij5y3tgzjz/Segment%20movement%20between%20two%20dates.pbix?dl=0

You Sir, saved my day. This approach works for me well on 10^5 rows.

Hello @sturlaws,

 

That's magic! Thank you very much. I will test the solution on a larger dataset next week.

 

Best regards

Semih

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors