Calculate Student Journey

[nik1]

So I need to show student journey - find the number of student completed all the course in each month.
There are 2 tables.
1)  Courses details(Course master) Columns(ID, CourseName) 1-CourseA, 2-CourseB ...5-CourseE. 5 courses total. 

2) Transaction Table. Having details StudentId, CourseID, CompletedDate(DateTimeStamp) etc.

Lets say Student1 completed 2 courses(A and B) in May, 1 course(D) in June, 1 course(C) in July and 1 course(E) in Aug. Student Journey is completed in August.

Total student are 500, some will complete all 5 course in May, some in June etc.

Sample Data Screenshot for expected result

Any help is appreciated

[tamerj1 · Accepted solution]

Hi @nik1 
I have built my measure on top of @Jihwan_Kim's sample file. However, as per my understanding of your requirement I followed a completely different approach. https://www.dropbox.com/t/AMTGGmXFHZTBDa79

Count = 
VAR NumberOfCourses = COUNTROWS ( ALLSELECTED ( Course[CourseName] ) )
VAR CurrentDate = MAX ('Date'[Date] )
VAR Completed = 
    FILTER ( 
        ALL ( Data[StudentID] ), 
        VAR TableOnAndBefore = CALCULATETABLE ( Data,'Date'[Date] <= CurrentDate )
        VAR LastCompletedDate = MAXX ( TableOnAndBefore, Data[CompletedDate] )
        RETURN
            COUNTROWS ( TableOnAndBefore ) = NumberOfCourses 
                && MONTH ( CurrentDate ) = MONTH ( LastCompletedDate )
    )
RETURN
    COUNTROWS ( Completed )

[tamerj1 · Accepted solution]

Hi @nik1 
I have built my measure on top of @Jihwan_Kim's sample file. However, as per my understanding of your requirement I followed a completely different approach. https://www.dropbox.com/t/AMTGGmXFHZTBDa79

Count = 
VAR NumberOfCourses = COUNTROWS ( ALLSELECTED ( Course[CourseName] ) )
VAR CurrentDate = MAX ('Date'[Date] )
VAR Completed = 
    FILTER ( 
        ALL ( Data[StudentID] ), 
        VAR TableOnAndBefore = CALCULATETABLE ( Data,'Date'[Date] <= CurrentDate )
        VAR LastCompletedDate = MAXX ( TableOnAndBefore, Data[CompletedDate] )
        RETURN
            COUNTROWS ( TableOnAndBefore ) = NumberOfCourses 
                && MONTH ( CurrentDate ) = MONTH ( LastCompletedDate )
    )
RETURN
    COUNTROWS ( Completed )

[nik1]

Thanks you @tamerj1 the solution is working good. 

There is little more tweek required if you can help. We also need to show Cummulative total/Running total for each month.
Like in the solution file June 2022 has 1 count, July 2022 has 0 count but R.Total for July 2022 should be 1.
Example -> Current Month Count + previous month total

[tamerj1 · Accepted solution]

Hi @nik1 
Here is the updated file as requested https://www.dropbox.com/t/3IXKtCQpmt9MwReQ

Count = 
VAR CurrentMonth = MAX ( 'Date'[Year Month Number] )
VAR NumberOfCourses = COUNTROWS ( ALLSELECTED ( Course[CourseName] ) )
VAR CurrentDate = MAX ( 'Date'[Date] )
VAR T1 = FILTER ( All ( Data ), Data[CompletedDate] <= CurrentDate )
RETURN
    SUMX ( 
        ALL ( Data[StudentID] ),
        VAR Student = Data[StudentID]
        VAR T2 = FILTER ( T1, Data[StudentID] = Student )
        RETURN
            IF ( COUNTROWS ( T2 ) = NumberOfCourses, 1, 0 )
    )

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below. I hope the below can provide some ideas on how to create a solution for your dataset.

All courses finish month: =
FORMAT (
    MAXX (
        GROUPBY (
            Data,
            Data[CompletedDate],
            "@lastcompleteddate", MAXX ( CURRENTGROUP (), Data[CompletedDate] )
        ),
        [@lastcompleteddate]
    ),
    "MMMM-YYYY"
)

[nik1]

Thanks @Jihwan_Kim  for replying and taking time to help here.
Update - I tried this solution as well, but results no as expected
Did not try you solution since it is not what the expected results  are.
I have update the post with sample data screenshot and expected results, if that can give more clarity.
Apprecaite your help

[amitchandak]

@nik1 , Assume total courses are 5

then

countx(filter( summarize(Table, Table[Student ID], "_1", calculate(distintcount(Table[CourseName])) ) , [_1] =5), [Student ID])

or

calculate(countx(filter( summarize(Table, Table[Student ID], "_1", calculate(distintcount(Table[CourseName])) ) , [_1] =5), [Student ID]), filter(Table, Table[Date] <= selectedvalue(Date[Date]) )

assuming the date is an independent date table

[nik1]

Thanks @amitchandak for replying and taking time to help here.
tried your suggestion but measure did not retun any results.
I have update the post with sample data screenshot and expected results, if that can give more clarity.
Apprecaite your help