Forum Discussion
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:
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
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
3 Replies
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
