Include individual if not in previous 2 months

DAX DAX Calculations
14

@jmwdba, I added both TermStart and MonthOffset columns to your Date table.

and also updated the Student count 2 measure to work of the Date MonthInCalendar column.

Student count 2 = 
VAR TermStart = LOOKUPVALUE( Dates[TermStart] | Dates[MonthInCalendar] | SELECTEDVALUE(Dates[MonthInCalendar]))
VAR MonthOffset = LOOKUPVALUE( Dates[MonthOffset] | Dates[MonthInCalendar] | SELECTEDVALUE( Dates[MonthInCalendar] ))
VAR OffsetNumber = IF( TermStart >0 | MonthOffset - TermStart | MonthOffset )

VAR StudentsEnrolledTermStart =
    CALCULATETABLE ( VALUES ( 'Enrolled data 2'[Student ID] )|
        FILTER ( ALL ( Dates )|
        Dates[MonthOffset] = OffsetNumber
        )| FILTER( 'Enrolled data 2' |
        'Enrolled data 2'[Enrollment Status] IN { "Enrolled in Future Term" | "Enrolled in Term" }
        )
    )
VAR StudentsEnrolled =
    CALCULATETABLE ( VALUES ( 'Enrolled data 2'[Student ID] )|
        FILTER ( ALL ( Dates )|
        Dates[MonthOffset] = MonthOffset
        )| FILTER( 'Enrolled data 2' |
        'Enrolled data 2'[Enrollment Status] IN { "Enrolled in Future Term" | "Enrolled in Term" }
        )
    )
RETURN

    COUNTROWS ( INTERSECT( StudentsEnrolled | StudentsEnrolledTermStart ) )

Here’s the updated file.
eDNA- Include individual if not in previous 2 months v2.pbix (444.0 KB)

1 Like