Continuous attendance for 7 days

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