cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dilumd
Solution Supplier
Solution Supplier

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
v-huizhn-msft
Microsoft
Microsoft

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




View solution in original post

4 REPLIES 4
Phil_Seamark
Microsoft
Microsoft

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!

v-huizhn-msft
Microsoft
Microsoft

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




View solution in original post

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.

Hi Angelia,

 

Thank you very much for the help 

Spoiler
 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors