Forum Discussion

SrMatto's avatar
SrMatto
Icon for Helper I rankHelper I
1 year ago
Solved

Calculating Completed Programs for Enrolled Persons on or after filter Dates

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...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.