cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dilumd Established Member
Established Member

Continuous attendance for 7 days

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: Continuous attendance for 7 days

Hi @dilumd,

I try to reproduce your scenario and get expected result.

I create sample data table.

1.PNG

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"


2.PNG

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")


3.png



If you have other issues, please let me know.

Best Regards,
Angelia




4 REPLIES 4
v-huizhn-msft Super Contributor
Super Contributor

Re: Continuous attendance for 7 days

Hi @dilumd,

I try to reproduce your scenario and get expected result.

I create sample data table.

1.PNG

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"


2.PNG

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")


3.png



If you have other issues, please let me know.

Best Regards,
Angelia




Super User
Super User

Re: Continuous attendance for 7 days

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

dilumd Established Member
Established Member

Re: Continuous attendance for 7 days

Hi Angelia,

 

Thank you very much for the help 

Spoiler
 
dilumd Established Member
Established Member

Re: Continuous attendance for 7 days

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

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

 

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

 

3.JPG

 

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.