Convert excel function to DAX

Anonymous 

This calculated column will give you the date on which the absence ENDS (will return only non-blank on the rows where an absence starts). Do note that the code is practically the same as the previous one, it just retunrs something different (a different VAR). The code calculates quite a number of VARS  so you can use them to return other things you might be interested in (such as the number of consecutive absence days)

Last date of absence =
IF (
    NOT AttendanceMaster[IsPresent];
    VAR NextPresence_ =
        CALCULATE (
            MIN ( AttendanceMaster[AttendanceDate] );
            ALLEXCEPT ( AttendanceMaster; AttendanceMaster[ChildID] );
            AttendanceMaster[AttendanceDate] > EARLIER ( AttendanceMaster[AttendanceDate] );
            AttendanceMaster[IsPresent] = TRUE ()
        )
    VAR lastAbsenceDay_ =
        CALCULATE (
            MAX ( AttendanceMaster[AttendanceDate] );
            ALLEXCEPT ( AttendanceMaster; AttendanceMaster[ChildID] );
            AttendanceMaster[AttendanceDate] < NextPresence_
        )
    VAR NumConsecutiveAbsentDays_ =
        CALCULATE (
            COUNT ( AttendanceMaster[AttendanceDate] );
            ALLEXCEPT ( AttendanceMaster; AttendanceMaster[ChildID] );
            AttendanceMaster[AttendanceDate] >= EARLIER ( AttendanceMaster[AttendanceDate] );
            AttendanceMaster[AttendanceDate] < NextPresence_
        )
    VAR isStartOfAbsence =
        VAR previousDate_ =
            CALCULATE (
                MAX ( AttendanceMaster[AttendanceDate] );
                ALLEXCEPT ( AttendanceMaster; AttendanceMaster[ChildID] );
                AttendanceMaster[AttendanceDate] < EARLIER ( AttendanceMaster[AttendanceDate] )
            )
        RETURN
            IF (
                ISBLANK ( previousDate_ );
                TRUE ();
                CALCULATE (
                    DISTINCT ( AttendanceMaster[IsPresent] );
                    ALLEXCEPT ( AttendanceMaster; AttendanceMaster[ChildID] );
                    AttendanceMaster[AttendanceDate] = previousDate_
                )
            )
    RETURN
        IF ( isStartOfAbsence; lastAbsenceDay_ )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers