Continuous attendance for 7 days

[dilumd]

Hi All,

I trying to identify the employees from my employees attendance data where they have report to work for 7 days continously (without getting any leave in between two days) during a period of two weeks?

Can anyone help me with that?

Dilum

[Phil_Seamark]

I'm sure this can be done using DISTINCTCOUNT of days over 7 day ranges. Can you please share some sample data.

[v-huizhn-msft · Accepted solution]

Hi @dilumd,

I try to reproduce your scenario and get expected result.

I create sample data table.



Right click your table->Edit Query, right click Customer and Attend Date column headers->Ascending, add an index column. Please see the Query statement and result.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjA0V4rVgQkYoQsYIwSc0LU4oWtxwq7FBN1QDAFTdAEzdAFzdAELdFswBCwxnG6AIYLhG0OYd2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Attend Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Attend Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Customer", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
    #"Sorted Rows1" = Table.Sort(#"Added Index",{{"Customer", Order.Ascending}, {"Attend Date", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Index"}),
    #"Added Index1" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1)
in
    #"Added Index1"

Then create calculated columns using the  formulas.

Ran = RANKX(FILTER(Record1,Record1[Customer]=EARLIER(Record1[Customer])),Record1[Attend Date],,ASC,Dense)

Last-Day = IF(Record1[Attend Date]=CALCULATE(MIN(Record1[Attend Date]),ALLEXCEPT(Record1,Record1[Customer])),BLANK(),LOOKUPVALUE(Record1[Attend Date],Record1[Index],Record1[Index]-1))

Differen between current and last day = IF(ISBLANK(Record1[Last-Day]),1,DATEDIFF(Record1[Last-Day],Record1[Attend Date],DAY))

Running total = CALCULATE(SUM(Record1[Differen between current and last day]),FILTER(ALLEXCEPT(Record1,Record1[Customer]),Record1[Ran]<=EARLIER(Record1[Ran])))

Finally, if you want to identify from your employees attendance data where they have report to work for 7 days, please use =7 in if function. Otherwise you can identify from your employees more than 7 days, or any x day you want. Please see the following formulas and result shown in screenshot.

Continuous attendance for 7 days = IF(CALCULATE(COUNTA(Record1[Customer]),FILTER(ALLEXCEPT(Record1,Record1[Customer]),Record1[Ran]=Record1[Running total]))=7,"Yes","No")

Continuous more than 7 days = IF(CALCULATE(COUNTA(Record1[Customer]),FILTER(ALLEXCEPT(Record1,Record1[Customer]),Record1[Ran]=Record1[Running total]))>=7,"Yes","No")

If you have other issues, please let me know.

Best Regards,
Angelia

[dilumd]

Hi @v-huizhn-msft

Sorry for adding this comment for a very old post, above solution worked fine for me until I notice examples like below doesn't capture in my report. example is as follows,

Customer     Date

A	9/15/2018
A	9/17/2018
A	9/18/2018
A	9/19/2018
A	9/20/2018
A	9/21/2018
A	9/22/2018
A	9/23/2018
A	9/25/2018
A	9/26/2018
A	9/27/2018
A	9/28/2018
A	9/29/2018

here actually this employee has attendance continuously for 7 days but it doesn't capture.

The problem is due to the condition (filter) where [Ran] should = to [Running total] above. 

Continuous attendance for 7 days = IF(CALCULATE(COUNTA('Attendance Records'[EMP_No]),FILTER(ALLEXCEPT('Attendance Records','Attendance Records'[EMP_No]),'Attendance Records'[Ran]='Attendance Records'[Running total])) =7,"Yes","No")

I couldn’t think of a solution for this, pls help..

Thank you in advance.

[dilumd]

Hi Angelia,

Thank you very much for the help 

Spoiler