Measure to Count Entries Using Latest Date Compared to a Dynamic Date

[Kryne7]

I'm pretty sure that some combination of VAR, CalculateTable, Summarize, or the like can get me a measure of what I'm wanting but I'm not having any luck.  I'm going to include sample data to hopefully explain the situation more clearly.

First, I have a table with survey data (Survey_Answers).  Each row is a single answer from a single employee but each employee will be answering the survey multiple times a year, as it goes out monthly.  Below, I have three columns where Employee_ID is the unique ID per employee, Round_End is when the specific survey round ended, and eNPS_Type that is either "Promoter", "Passive" or "Detractor."

Employee_ID	Round_End	eNPS_Type
00001	1/19/2025	Promoter
00001	2/16/2025	Promoter
00001	3/16/2025	Promoter
00002	1/19/2025	Passive
00002	2/16/2025	Detractor
00002	3/16/2025	Passive
00003	1/19/2025	Promoter
00003	2/16/2025	Promoter
00004	2/16/2025	Detractor
00004	3/16/2025	Promoter

The way the calculation needs to work, is that on a line graph (or using a slicer that the user can pick from) each Round_End date needs to count the distinct count of Employee_ID of Promoters, Passives, and Detractors but using the latest date before or equal to the selected Round_End Date.

So below is what I would expect the "calculated table" for each of these months to look like and the subsequent count of each of the three categories.

January

Employee_ID	Round_End	eNPS_Type
00001	1/19/2025	Promoter
00002	1/19/2025	Passive
00003	1/19/2025	Promoter

Promoters - 2, Passives - 1, Detractor - 0

February

Employee_ID	Round_End	eNPS_Type
00001	2/16/2025	Promoter
00002	2/16/2025	Detractor
00003	2/16/2025	Promoter
00004	2/16/2025	Detractor

Promoters - 2, Passives - 0, Detractors - 2

March

Employee_ID	Round_End	eNPS_Type
00001	3/16/2025	Promoter
00002	3/16/2025	Passive
00003	2/16/2025	Promoter
00004	3/16/2025	Promoter

Promoters - 3, Passive - 1, Detractor - 0

I highlighted the answer from Employee_ID 00003 in the third example because I feel that this is where the complication comes in.  This specific employee didn't take the survey in March, so the count needs to include that person's "latest" answer which was the 2/16 response.

I have a dimension table with a column of just the dates of the Round_End but I'm not sure if that is needed.  

In the end, I'd like a measure to give me the total count of entries that meet these requirements, and then a measure for counts for each of the three categories.

As an added bonus, there is one more wrinkle but it might not be a big deal after I figure this out, but I'll throw it here too.  In the first Survey_Answers table, there is a Termination_Date column for any employees who have taken the survey but were then terminated.  We want to include their entries for 3 months from their termination date and then filter them out after that.  I created an Inclusion_Date column that just moves the termination date forward 12 weeks, which then gives the date that after, we don't want to include their survey answers anymore.  I'm not sure if this part can be built into the measure I'm asking about up above or if this needs to be a seperate thing.

Any help is greatly appreciated!  Thank you.

Kristofer

[parry2k]

@Kryne7 I have couple of videos on my YT channel on INDEX function:

Let's explore INDEX function in Power BI and learn how it is different from other WINDOW functions

Learn how easy to get top sales by each category using INDEX WINDOW function in Power BI

[parry2k · Accepted solution]

@Kryne7 try following measure, use round_end date from the dimension table you have created, make sure that table doesn't have relationship with the main table:

Top = 
VAR __SelectedDate = SELECTEDVALUE ( Round_End[Round_End] )

VAR __Return = 
CALCULATE ( 
    COUNTROWS ( 'Table1' ),
    INDEX (
            1,
            SUMMARIZE ( FILTER ( ALL ( Table1 ), Table1[Round_End] <= __SelectedDate ), Table1[Employee_ID], Table1[Round_End]),
            ORDERBY ( Table1[Round_End], DESC ),
            PARTITIONBY ( Table1[Employee_ID] )
            
        )
)
RETURN __Return 

[Kryne7]

Thank you!  That worked perfectly.

I've never used the Index function but thanks to your solution, I think I've learned about a new tool for the tool set.