Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
I am trying to solve an issue I have. We receive data from customers about the satisfaction of the service received. The simplified data of the Survey table looks something like this:
Survey ID | Version | DET / PRO | Date |
1 | 1 | PRO | 01/01/2023 |
2 | 1 | DET | 01/01/2023 |
3 | 1 | PRO | 01/01/2023 |
4 | 1 | PRO | 01/01/2023 |
5 | 1 | DET | 01/01/2023 |
6 | 1 | PRO | 01/01/2023 |
7 | 1 | PRO | 01/02/2023 |
8 | 1 | DET | 01/02/2023 |
9 | 1 | PRO | 01/02/2023 |
10 | 1 | 01/02/2023 | |
6 | 2 | DET | 01/02/2023 |
11 | 1 | PRO | 01/03/2023 |
12 | 1 | PRO | 01/03/2023 |
13 | 1 | 01/03/2023 | |
14 | 1 | PRO | 01/03/2023 |
15 | 1 | PRO | 01/03/2023 |
16 | 1 | DET | 01/03/2023 |
17 | 1 | PRO | 01/03/2023 |
18 | 1 | PRO | 01/03/2023 |
19 | 1 | DET | 01/04/2023 |
10 | 2 | PRO | 01/04/2023 |
20 | 1 | PRO | 01/04/2023 |
21 | 1 | DET | 01/04/2023 |
22 | 1 | PRO | 01/05/2023 |
23 | 1 | PRO | 01/05/2023 |
16 | 2 | DET | 01/05/2023 |
24 | 1 | PRO | 01/05/2023 |
25 | 1 | PRO | 01/05/2023 |
26 | 1 | DET | 01/05/2023 |
27 | 1 | PRO | 01/06/2023 |
28 | 1 | 01/06/2023 | |
29 | 1 | DET | 01/06/2023 |
30 | 1 | PRO | 01/06/2023 |
31 | 1 | PRO | 01/06/2023 |
32 | 1 | DET | 01/06/2023 |
16 | 3 | 01/07/2023 | |
33 | 1 | PRO | 01/07/2023 |
34 | 1 | DET | 01/07/2023 |
35 | 1 | PRO | 01/07/2023 |
We want to calculate the NPS (Net promoter score) for a rolling 12 months period. That is the difference between surveys with PRO int the PRO/DET column and surveys with DET over the sum of all surveys (including those with blank values in the PRO/DET column). The basic measures would be:
There is however an issue: customers can take the survey multiple times and we need to take into account only the latest version of the survey (Version column in the data).
So if we take for example survey 16 in the above data, it has 3 versions in different months and it needs to be counted as a DET survey in the rolling periods up to July and from July onwards as a neutral survey (so only counted in the total count). It also obviously only needs to be counted once should there be multiple versions in the period considered.
What i need to achieve is that the maximum version is calculated within each rolling period for each ID and only the rows with the max version be counted by the measure Survey Count.
I know the way to go is probably to use SUMMARIZECOLUMNS but I am just not knowleadgeable enough to write it properly.
Any help would be appreciated! Thank you in advance.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |