cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Solution Supplier

## Continuous Attendance

Hi Everyone,

I have below table with employee attendance data from which I need to identify whether there is any employee who has reported to work continuously for 7 days or more. Pls help, may data look like this. Thank you inadvance !

 EMP No Date 1 9/17/2018 1 9/18/2018 1 9/19/2018 1 9/20/2018 1 9/21/2018 1 9/22/2018 1 9/23/2018 1 9/25/2018 1 9/26/2018 1 9/27/2018 1 9/28/2018 1 9/29/2018 1 9/30/2018 2 9/17/2018 2 9/19/2018 2 9/20/2018 2 9/21/2018 2 9/22/2018 2 9/23/2018 2 9/24/2018 2 9/25/2018 2 9/27/2018 2 9/28/2018 2 9/29/2018 2 9/30/2018 3 9/17/2018 3 9/19/2018 3 9/20/2018 3 9/21/2018 3 9/22/2018 3 9/24/2018 3 9/25/2018 3 9/27/2018 3 9/28/2018 3 9/29/2018 3 9/30/2018 4 9/17/2018 4 9/18/2018 4 9/19/2018 4 9/20/2018 4 9/21/2018 4 9/22/2018 4 9/23/2018 4 9/24/2018 4 9/25/2018 4 9/26/2018 4 9/27/2018 4 9/28/2018 4 9/29/2018 4 9/30/2018
1 ACCEPTED SOLUTION
Community Champion

@dilumd

Try this calculated column.

It will identify the first day when consecutive 7 days start for each employee

You can then use it as VISUAL filter

```Continuously_Present =
VAR mycontinuousdays = 7
RETURN
IF (
[Date] + mycontinuousdays
- 1
= CALCULATE (
MIN ( [Date] ),
FILTER (
Table1,
[EMP No] = EARLIER ( [EMP No] )
&& [Index]
= EARLIER ( [Index] ) + mycontinuousdays
- 1
)
),
"Present"
)
```

Regards
Zubair

2 REPLIES 2
Community Champion

@dilumd

Try this calculated column.

It will identify the first day when consecutive 7 days start for each employee

You can then use it as VISUAL filter

```Continuously_Present =
VAR mycontinuousdays = 7
RETURN
IF (
[Date] + mycontinuousdays
- 1
= CALCULATE (
MIN ( [Date] ),
FILTER (
Table1,
[EMP No] = EARLIER ( [EMP No] )
&& [Index]
= EARLIER ( [Index] ) + mycontinuousdays
- 1
)
),
"Present"
)
```

Regards
Zubair

Solution Supplier

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors