DAX Help - Struggled for consecutive days DAX

[Anonymous]

Hi all of my friends,

I have struggled to create DAX for counting of ChilID who has FALSE (Column IsPresent) for 3+ Consecutive Days Filter by Each Month.

I have made manually counting and I have the result as the table below:

Child ID	March	April	May
11	2	1	1
12	1	1	1
13	1	1	1
14	1	2	2
15	2	2	2

Please help me with the DAX to count the child ID who has FALSE (Column IsPresent) for 3+ Consecutive Days Filter by Each Month.
Your help is very important to me.

I have attached the sample data on this link below:

Sample Data 

Best,

Lorens

[Icey]

Hi @Anonymous ,

Based on your current data sort, add an Index column in Power Query, then create calculated columns like so:

IsConsecutive = 
VAR NextIndex = AttendanceMaster[Index] + 1
VAR Next2Index = AttendanceMaster[Index] + 2
VAR ThisDate_ = AttendanceMaster[AttendanceDate]
VAR NextDate =
    CALCULATE (
        MAX ( AttendanceMaster[AttendanceDate] ),
        FILTER ( AttendanceMaster, AttendanceMaster[Index] = NextIndex )
    )
VAR Next2Date =
    CALCULATE (
        MAX ( AttendanceMaster[AttendanceDate] ),
        FILTER ( AttendanceMaster, AttendanceMaster[Index] = Next2Index )
    )
VAR DateDiff1 =
    DATEDIFF ( ThisDate_, NextDate, DAY )
VAR DateDiff2 =
    DATEDIFF ( ThisDate_, Next2Date, DAY )
VAR IsConsecutive =
    IF (
        AttendanceMaster[IsPresent] = FALSE (),
        IF ( DateDiff1 = 1 && DateDiff2 = 2, 1, 0 )
    )
RETURN
    IsConsecutive

Count 0 Value = 
CALCULATE (
    COUNT ( AttendanceMaster[IsConsecutive] ),
    FILTER (
        'AttendanceMaster',
        AttendanceMaster[IsConsecutive] = 0
            && AttendanceMaster[IsPresent] = FALSE ()
            && 'AttendanceMaster'[AttendanceDate]
                <= EARLIER ( 'AttendanceMaster'[AttendanceDate] )
            && AttendanceMaster[ChildID] = EARLIER ( AttendanceMaster[ChildID] )
    )
)

MaxDate = 
VAR MaxDate =
    CALCULATE (
        MAX ( AttendanceMaster[AttendanceDate] ),
        FILTER (
            ALL ( AttendanceMaster ),
            AttendanceMaster[ChildID] = EARLIER ( AttendanceMaster[ChildID] )
                && AttendanceMaster[Count 0 Value] = EARLIER ( AttendanceMaster[Count 0 Value] )
                && AttendanceMaster[AttendanceDate].[MonthNo]
                    = EARLIER ( AttendanceMaster[AttendanceDate].[MonthNo] )
        )
    )
RETURN
    IF ( AttendanceMaster[IsConsecutive] = 1, MaxDate )

Then, create a measure.

Count = 
CALCULATE (
    DISTINCTCOUNT ( AttendanceMaster[MaxDate] ),
    FILTER (
        AttendanceMaster,
        AttendanceMaster[MaxDate] <> BLANK ()
            && AttendanceMaster[ChildID] = MAX ( AttendanceMaster[ChildID] )
            && AttendanceMaster[AttendanceDate].[MonthNo]
                = MAX ( AttendanceMaster[AttendanceDate].[MonthNo] )
    )
)

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Johanno]

Hi, my already posted solution contained one calculated column. Yours contain three columns plus a measure. Wouldn't it be better to keep the model as clean as possible and avoid many calculated columns to save memory if the data grows big?

/Johan

[Icey]

Hi @Johanno  ,

I just provide another workaround. Of course, your method is more memory-efficient.👍

But I think you need another filter for Date. Some dates are not consecutive. This needs to be considered, doesn't it?

Best Regards,

Icey

[Johanno]

Ok, since we sort the id and dates in order it doesn't matter if there are gaps when we add the index column. We want to see how many groups there are of at least three consecutive "false" and get a 1 per group. Then we can put that column in a matrix and get the result.

[Anonymous]

@Johanno @Icey 

I agree with @Johanno 

The calculation column that @Johanno has created is better.

Thanks.

But in other cases, I want to identify the calculate column for the first date of consecutive absent and calculate column for the last date of consecutive absent then I want to measure the number of the days between the first date and the last date of consecutive absent.

Can you help me with this?

Thanks

[Johanno]

I don't seem to find a solution to sum the days for each group of false days, maybe Power Query / M is more suitable if you can group them by occasion and add a sum for false days.

Tried to create a column with cumlative sum only if days are false, like:

Cumlative days present false = 
VAR CurrentRowIndex = data[Index]
VAR CumulativeDaysFalse = CALCULATE( COUNTROWS(data), FILTER(data, 
                            data[IsPresent] = False &&
                            data[Index] < CurrentRowIndex)
)
RETURN CumulativeDaysFalse

Then I want to reset the counter for each new group, but I don't think it's possible to access the previous row's value within the same column...?

I know it's not exactly what you're asking for but I was thinking if a simple measure that counts days that are false might work:

Count false days as a measure = CALCULATE( COUNTROWS(data), data[IsPresent] = False)

Then you can filter/slice it as you want, like per ChildID and month or week:

But there you'll only see total number of days per month and not number of periods with false days. Maybe Icey or someone else can give us an answer. 😊

[Anonymous]

@Johanno  and @Icey 

I have made column calculation like this one:

Number of Consecutive absence Days = 
IF (
    NOT AttendanceMaster[IsPresent],
    VAR NextPrecense_ =
        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] < NextPrecense_
        )
    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_
                )
            )
    VAR FirstDateOfConsecutiveAbsent =
        IF ( IsStartofAbsence, AttendanceMaster[AttendanceDate] )
    VAR LastDateOfConsecutiveAbsent =
        IF ( IsStartofAbsence, LastAbsenceDay_ )
    VAR TBL_Date =
        CALENDAR ( FirstDateOfConsecutiveAbsent, LastDateOfConsecutiveAbsent )
    VAR TBL_FinalDate =
        ADDCOLUMNS ( TBL_Date, "AbsentDays", IF ( WEEKDAY ( [Date], 3 ) >= 6, 0, 1 ) )
    RETURN
        SUMX ( TBL_FinalDate, IF ( [AbsentDays] = 1, 1, 0 ) )
)

but The start date or end date in Calendar function can not be Blank value.

Maybe you can try to Evaluate this Column DAX

Thank you

Best,

LA

[Johanno]

Hello again, I havn't tried to understand that formula. But an alternative that gives you a new table regardless of dates and just looks a the connected days as sequences. Not fine tuned and maybe not a good solution but may help on the way:

First sorted, created index and used the old column that sets a 1 on the last the of false in that sequence. Then used that column to create another column that sets the number of the sequence (1, 2, 3 etc)

Sequence no = 
VAR CurrentRowIndex = data[Index]
RETURN IF( data[IsPresent]=False, 
            CALCULATE( SUM(data[Threetimesfalse]), FILTER(data, data[Index] < CurrentRowIndex))+1, 
            BLANK()
)

Then we can use that column to summarize to a new table:

Summarized table = 
VAR FilterDataTable = FILTER(data, data[IsPresent] = FALSE())
RETURN SUMMARIZECOLUMNS(data[ChildID], data[Month], data[Sequence no], "No days false in that sequence", SUM(data[CheckIfFalse]))

A calculated table like this might not be pleasant, but by the column above we could get a unique index for each sequence of false and then you can shape in more ways like:

I think I'm out of ideas. 😊

[Johanno]

Hi, I think you first need an index column to work with in order to skip missing dates and collect values from next row. This worked for me:

1. Power Query: First sort the data by ChildID and then AttendanceDate

2. Power Query: Add Column - Index column

3. Create a calculated column:

Threetimesfalse = 
VAR CurrentRow = data[Index]
VAR CurrentID = data[ChildID]
VAR CountFalse = CALCULATE( COUNTROWS(data), FILTER( data,
                            data[IsPresent] = False &&
                            data[ChildID] = CurrentID &&
                            data[Index] <= CurrentRow &&
                            data[Index] >= CurrentRow -3
                            
))
VAR NextRow = LOOKUPVALUE(data[IsPresent], data[Index], CurrentRow+1)
RETURN
    IF( CountFalse >= 3 && 
        NextRow=True && 
        data[IsPresent]=False, 
    1, 
    BLANK()
)

Then you get a table like:

which you can add to a matrix and get the layout you wanted.

/Johan

[AllisonKennedy]

Do you have a DimDate table?

[Anonymous]

@AllisonKennedy ,

No, I dont have

[AllisonKennedy]

I think you'll need one to do this properly: https://allisonkennedycv.blogspot.com/2020/04/dimdate-what-why-and-how.html

I'll see if I have time to give this proper consideration this weekend, but start reading up on DimDate tables in the meantime.

[Anonymous]

As your information:

1 March 2020, 8 March 2020, 15 March 2020, 22 March 2020, 29 March 2020, 5 April 2020, 12 April 2020, 19 April 2020, 26 April 2020, 3 May 2020, 10 May 2020, 17 May 2020, 24 May 2020 and 31 May 2020 is SUNDAY or Holiday

Example:

If Child ID has FALSE on 6 March 2020, 7 March 2020 and 9 March 2020, then Child has FALSE for 3 consecutive days because 8 March 2020 is Sunday or holiday.

Thank you