Count IDS that transferred from one program to another based on start and end date columns

[Joacb761]

Hello! 

I can't seem to figure out how to set up the dax to calculate this (I'm relatively new to DAX). I'm hoping to create either a table visual or bar graph that I can filter using a Date filter to see how many IDs transferred from Program A to Program be in a given year. 

Below shows how my table is set up. A user/unique ID may have been in one of the programs more than once, but can not be in Program B while still in program A. If a user is still enrolled in a program, their end date is null.

In this example, I am hoping to filter to the year 2020 and show that 2 users transferred from Program A to Program B (51001 and 51002).

Row	unique ID	Program Name	Start Date	End Date
1	51001	Program A	6/1/2010	9/1/2012
2	51001	Program A	8/6/2014	6/30/2020
3	51001	Program B	7/2/2020
4	51002	Program A	4/1/2016	9/1/2020
5	51002	Program B	10/1/2020	5/1/2021
6	51003	Program A	4/1/2016	6/1/2018
7	51003	Program B	7/1/2018

[AlB · Accepted solution]

Hi @Joacb761 

See the attached file for a possible solution

Measure = 
VAR startedProgramB =
    CALCULATETABLE (
        DISTINCT ( Table1[unique ID] ),
        Table1[Program Name] = "Program B"
    )
VAR leftProgramA =
    CALCULATETABLE (
        DISTINCT ( Table1[unique ID] ),
        USERELATIONSHIP ( DateT[Date], Table1[End Date] ),
        Table1[Program Name] = "Program A"
    )
RETURN
    COUNTROWS ( INTERSECT ( startedProgramB, leftProgramA ) )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.