Forum Discussion

Joacb761's avatar
Joacb761
Frequent Visitor
5 years ago
Solved

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

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 IDProgram NameStart DateEnd Date
151001

Program A

6/1/20109/1/2012
251001

Program A

8/6/20146/30/2020
351001Program B7/2/2020 

4

51002

Program A4/1/20169/1/2020

5

51002

Program B10/1/20205/1/2021

6

51003

Program A4/1/20166/1/2018

7

51003

Program B7/1/2018 
COUNT
DAX
Start and end period
  • AlB's avatar
    AlB
    5 years ago

    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.

     

1 Reply

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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.