Segment movement between two dates

[Semih87]

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:

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

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

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

[sturlaws · Accepted solution]

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

[RolandasT]

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

[Semih87]

Hello @sturlaws,

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

Best regards

Semih