Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |