Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RSchenk
New Member

Rolling Period with dynamic field

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 IDVersionDET / PRODate
11PRO01/01/2023
21DET01/01/2023
31PRO01/01/2023
41PRO01/01/2023
51DET01/01/2023
61PRO01/01/2023
71PRO01/02/2023
81DET01/02/2023
91PRO01/02/2023
101 01/02/2023
62DET01/02/2023
111PRO01/03/2023
121PRO01/03/2023
131 01/03/2023
141PRO01/03/2023
151PRO01/03/2023
161DET01/03/2023
171PRO01/03/2023
181PRO01/03/2023
191DET01/04/2023
102PRO01/04/2023
201PRO01/04/2023
211DET01/04/2023
221PRO01/05/2023
231PRO01/05/2023
162DET01/05/2023
241PRO01/05/2023
251PRO01/05/2023
261DET01/05/2023
271PRO01/06/2023
281 01/06/2023
291DET01/06/2023
301PRO01/06/2023
311PRO01/06/2023
321DET01/06/2023
163 01/07/2023
331PRO01/07/2023
341DET01/07/2023
351PRO01/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:

 

1. Survey Count = COUNTROWS(Survey)
 
2. NPS =
VAR PRO = CALCULATE([Survey Count],Survey[DET / PRO] = "PRO")
VAR DET = CALCULATE([Survey Count],Survey[DET / PRO] = "DET")
RETURN
DIVIDE(PRO-DET,[Survey Count])
 
3.  NPS 12 MO =
CALCULATE(NPS_Measures[NPS], DATESBETWEEN('Date'[Date], EDATE(DATE(YEAR(MAX('Date'[Date])),MONTH(MAX('Date'[Date])),01),-12), MAX('Date'[Date])))

 

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.

 

 

0 REPLIES 0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.