Calculating Completed Programs for Enrolled Persons on or after filter Dates

[SrMatto]

Hi
I'm trying to achieve the following calculation:

"Count of all the program that are completed on or after some enrollment date".

In the context of this data model:

For all the persons enrolled on one or more IdDate, count all FactCompletion[Program] that have a FactCompletion[degree of completion]="Complete", on or after the selected enrollment dates 

Note that the data model structure with the bridge table may not be changed (other Facts have been removed).

For example, for semeter 2 of 2020 and 2021, the enrolled are:

For these PersonId, the completion on or after is:

Then, the expected result is:

link to pbix 

[VahidDM]

Hi @SrMatto 

To calculate the count of programs that are completed on or after the enrollment dates for persons enrolled on selected dates, you can use the following DAX measure:

CompletedPrograms =
SUMX(
    SUMMARIZE(
        FILTER(
            FactEnrollment,
            FactEnrollment[IdDate] IN SELECTEDVALUES(FactEnrollment[IdDate])
        ),
        FactEnrollment[PersonId],
        FactEnrollment[IdDate]
    ),
    VAR CurrentPerson = FactEnrollment[PersonId]
    VAR EnrollmentDate = FactEnrollment[IdDate]
    RETURN
        CALCULATE(
            DISTINCTCOUNT(FactCompletion[Program]),
            FactCompletion[PersonId] = CurrentPerson,
            FactCompletion[Degree of completion] = "Complete",
            FactCompletion[CompletionDate] >= EnrollmentDate
        )
)

Explanation:

• SUMMARIZE creates a table of enrolled persons and their enrollment dates based on the selected IdDate.
• SUMX iterates over each person and enrollment date.
• CALCULATE counts the distinct programs where:
  • The PersonId matches.
  • Degree of completion is "Complete".
  • CompletionDate is on or after the EnrollmentDate.
• The measure sums up the counts to provide the total number of completed programs for the enrolled persons.

Notes:

• Ensure your table names (FactEnrollment, FactCompletion) and column names match those in your data model.
• This measure respects the selected enrollment dates and filters the completions accordingly.
• No changes to your data model are required.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[SrMatto]

Hi VahidDm,
Thanks, but it doesn't work. For start, SELECTEDVALUES doesn't exist in DAX. I changed it to VALUES to get the desired result, though.
Also, CompletionDate is not part of the data model. (Replaced it with Factcompletion[idDate])
Still, even with this correction, calculation doesn't gives the correct answer. 

[VahidDM]

You right, it should be SELECTEDVALUE 🙂

[v-zhengdxu-msft]

Hi @SrMatto 

Please try this:

Here I create a measure:

Count =
VAR _values =
    SUMMARIZE ( ALLSELECTED ( FactEnrollment ), 'FactEnrollment'[Person ID] )
VAR _vtable =
    FILTER (
        ALL ( FactCompletion ),
        'FactCompletion'[Person ID]
            IN _values
                && 'FactCompletion'[Degree of Completion] = "Complete"
    )
RETURN
    IF (
        ISINSCOPE ( FactCompletion[Program] ),
        COUNTROWS (
            FILTER ( _vtable, [Program] = SELECTEDVALUE ( 'FactCompletion'[Program] ) )
        ),
        COUNTROWS ( _vtable )
    )

Then drag it to the table visual to replace the original [Count] field so that the result is as follow:

The PBIX file is attached.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SrMatto]

Thank you Zhengdong Xu,

It is very close, but unfortunately, does not generalize.

For instance, in year 2022 semester 1 (20221) yours return 2, but doing it manually gives 1:

[v-zhengdxu-msft]

Hi @SrMatto 

I'm a little confused. If possible, please tell me about the logic.

Here's how I understand it:
___________________________________________________________________________________________

In the first case, the 2 of the sport is made up of 20212 and 20232.

In the logic, we can get 5,7,8,10,13,15 from FactEnrollment table:

Then filter these ID in the FactCompletion table, so that the result is 1(Craftsman)+2(Sport)

Same to the second case, the result should be 2.

___________________________________________________________________________________________

If there is something I‘ve misunderstood, please point it out.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SrMatto]

Hi @v-zhengdxu-msft ,
Thank you for the follow up.
To clarify:  I think you might be missing the condition "on or after DateID"

• The first example is correct.
• The second example, result should be 1 because personID 13 has completed on 2021 which is PRIOR to the IdDate filter (set as 2022, semester 1)
  
  

[v-zhengdxu-msft]

Hi @SrMatto 

Please try this measure:

Count =
VAR _Slicer =
    MIN ( 'DimDate'[IdDate] )
VAR _values =
    SUMMARIZE ( ALLSELECTED ( FactEnrollment ), 'FactEnrollment'[Person ID] )
VAR _vtable =
    FILTER (
        ALL ( FactCompletion ),
        'FactCompletion'[Person ID]
            IN _values
                && 'FactCompletion'[Degree of Completion] = "Complete"
                && 'FactCompletion'[IdDate] >= _Slicer
    )
RETURN
    IF (
        ISINSCOPE ( FactCompletion[Program] ),
        COUNTROWS (
            FILTER ( _vtable, [Program] = SELECTEDVALUE ( 'FactCompletion'[Program] ) )
        ),
        COUNTROWS ( _vtable )
    )

 The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SrMatto]

Hi @v-zhengdxu-msft ,
It's very cool. Thanks.
I works almost perfectly!
Still, I found an glitch. How would you solve the fact that: if the Program is not present in the Enrollment YearSemester, it don't show up?
Look:
For IdDate=20202:

Yet, your (almost perfect!) calculation returns:

Which I believe is because "Sport" don't appear in 20202

[v-zhengdxu-msft · Accepted solution]

Hi @SrMatto 

Please try this:

Create a new table:

Table = VALUES(FactCompletion[Program])

Then change the 'FactCompletion'[Program] into 'Table'[Program] in the table visual:

Then I did some change on the [Count] measure:

Count =
VAR _Slicer =
    MIN ( 'DimDate'[IdDate] )
VAR _values =
    SUMMARIZE ( ALLSELECTED ( FactEnrollment ), 'FactEnrollment'[Person ID] )
VAR _vtable =
    FILTER (
        ALL ( FactCompletion ),
        'FactCompletion'[Person ID]
            IN _values
                && 'FactCompletion'[Degree of Completion] = "Complete"
                && 'FactCompletion'[IdDate] >= _Slicer
    )
RETURN
    IF (
        ISINSCOPE ( 'Table'[Program] ),
        COUNTROWS ( FILTER ( _vtable, [Program] = SELECTEDVALUE ( 'Table'[Program] ) ) ),
        COUNTROWS ( _vtable )
    )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SrMatto]

Thank you so much @v-zhengdxu-msft !

[SachinNandanwar]

What about the other personid's apart from 1,10,13 who have also Completed the program ?

[SrMatto]

in the example, only the ones that have enrollment in the selected dateId must be considered for the calculation. (2,6,16,17 don't count as complete)