Anonymous

Not applicable

6 years ago

Solved

Convert excel function to DAX

Hi, Can somebody help me to convert this excel pivot table function to DAX Measure =COUNTIFS ([Child ID],[@[Child ID]],[Child is Present?], FALSE(),[Attendance Date],"<="&([@[Attendance Date...

6 years ago

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

Greg_Deckler

Community Champion

6 years ago

Anonymous - See if this assists: https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991

AlB

Community Champion

6 years ago

Anonymous

So you do not need a measure but calculated columns, it seems. In any case, please explain the ultimate result you want out of this, instead of talking about translating formulae from Excel. The approach in DAX will probably be different. Do you want to identify the children that have been abstent at least three days in a row? Do you want to count them? What do you need exactly???

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

Anonymous

Not applicable

6 years ago

Yes, I want to count the children that have been abstent at least three days in a row. But in other case I need to identify the absent start date and absent end date for each children by adding 2 colomn on BI. How about your opinion?

AlB

Community Champion

6 years ago

Anonymous

This calculate column will give you the date on which the absence starts (will return only non-blank on the rows where an absence starts):

First 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; AttendanceMaster[AttendanceDate] )
)

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

AlB

Community Champion

6 years ago

Anonymous

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

Forum Discussion

Convert excel function to DAX

Related content

Convert Excel functions to DAX

Convert Excel columns into rows in a PBI table

function to convert date to integer

Convert Excel 'search' function to DAX PowerBi

Convert string to number in DAX Function