Convert excel function to DAX

Anonymous 

Start date of the student who had absent 3 or more days in a month.

start Date = 
VAR countVal =
    COUNTX (
        FILTER (
            AttendanceMaster,
            'AttendanceMaster'[ChildID]
                = EARLIER ( 'AttendanceMaster'[ChildID] )
                && MONTH ( 'AttendanceMaster'[AttendanceDate] )
                    = MONTH (
                        EARLIER ( AttendanceMaster[AttendanceDate] )
                    )
                && AttendanceMaster[IsPresent]
                    = FALSE ()
        ),
        AttendanceMaster[AttendanceDate]
    )
VAR result =
    IF (
        countVal >= 3,
        MINX (
            FILTER (
                AttendanceMaster,
                AttendanceMaster[ChildID]
                    = EARLIER ( AttendanceMaster[ChildID] )
                    && MONTH ( AttendanceMaster[AttendanceDate] )
                        = MONTH (
                            EARLIER ( AttendanceMaster[AttendanceDate] )
                        ) && AttendanceMaster[IsPresent]
                    = FALSE ()
            ),
            AttendanceMaster[AttendanceDate]
        ),
        BLANK ()
    )
RETURN
    result

End date of the student who had absent 3 or more days in a month.

end Date = 
VAR countVal =
    COUNTX (
        FILTER (
            AttendanceMaster,
            'AttendanceMaster'[ChildID]
                = EARLIER ( 'AttendanceMaster'[ChildID] )
                && MONTH ( 'AttendanceMaster'[AttendanceDate] )
                    = MONTH (
                        EARLIER ( AttendanceMaster[AttendanceDate] )
                    )
                && AttendanceMaster[IsPresent]
                    = FALSE ()
        ),
        AttendanceMaster[AttendanceDate]
    )
VAR result =
    IF (
        countVal >= 3,
        MAXX(
            FILTER (
                AttendanceMaster,
                AttendanceMaster[ChildID]
                    = EARLIER ( AttendanceMaster[ChildID] )
                    && MONTH ( AttendanceMaster[AttendanceDate] )
                        = MONTH (
                            EARLIER ( AttendanceMaster[AttendanceDate] )
                        ) && AttendanceMaster[IsPresent]
                    = FALSE ()
            ),
            AttendanceMaster[AttendanceDate]
        ),
        BLANK ()
    )
RETURN
    result

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

Here is my latest blog

https://community.powerbi.com/t5/Community-Blog/Dynamic-Page-Navigation-Based-on-User-Login/ba-p/1097786

AlB ,

I am sorry, 

Your DAX not work on my BI because "A table of multiple values was supplied where a singel value was expected".

How to fix this? 

nandukrishnavs ,

I will try this. Thank you. I will let you know if this DAX work well

AlB , Pleaee attach 

Anonymous 

Are you running it on the same data you shared? It's working fine on my side

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 

nandukrishnavs ,

Please attach the Power BI file

I have running it on my true data. Do you want to see my data? So that you can know well

Anonymous 

Yes, please share it. I think I have an idea of what the issue might be (edge cases, of course) but I need the real data to check it out.

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 ,

This is link of MyData. 

Please look on it. 

My Data 

Thank you

Anonymous 

Ok, I had a look. The code is doing what it should. The problem is that there are instances where your data has, for the same day and child, two (or more) rows and with values both TRUE and FALSE in the column isPresent.

One example of this are rows with AttendanceID 774 and 779, for childID 43 on 07/03/2018.  

So that's actually a problem with the data, not with the code. I guess it doesn't make sense to have to rows with inconsistent attendance info for the same date and child, does it? In fact I don't think you ought to have more than one a row for the same date/child at all (even if the info is the same in all those rows).

It is the DISTINCT that causes the error (see in red below). But again, the problem is the data. Why do you have cases like that? It would be a matter of deciding how you want to treat those IF(ISBLANK(previousDate_),TRUE(),CALCULATE(DISTINCT(AttendanceMaster[IsPresent]),ALLEXCEPT(AttendanceMaster,AttendanceMaster[ChildID]),AttendanceMaster[AttendanceDate]=previousDate_))

In fact, there are more oddities with the data. An extreme case is childID 2966 on date 10/08/2019. The info for that date and child is repeated in 15 rows!! 🤔

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 ,

Thanks Bro. Your input is so perfect. I also think so. I will cleaned the data again. 

But for the last, can you help me again to created DAX to add coloumn start date and end date fot the child who has absent 3 or more days in every month? Not Absent 3 or more consecutive days. Please help me bro if you have a time. I am sorry make you bussy. 

Thank you very much