Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Daximillian
New Member

Calculate percentage of employees not exceeding 5 consecutive days per week, with measures only

I am hitting a wall trying to create a measure that calculates the number of employees not exceeding 5 days worked consecutively per week (defined as Monday - Sunday, not a 7 day period).

 

So far I have only been able to achieve it by first creating a calculated table with a summary of dates, employee id's, year_week and year_month, then some additional calculated columns and finally two measures. The issue with this is that since I am pulling the employee id's from the employee table and the dates, year_month and year_week from the date table, I cannot visualize it on a table visual that uses any date field (in my case I use year_month), or use any of my dimension table slicers related in any way to th date or employee table, and I cannot create a relationship back to the tables as it creates circular references.

My initial table:

 

 

 

FiveDaysWorkedInArowPerWeek =
SUMMARIZECOLUMNS(
'm d_employees'[employee_id],
'm d_date'[date_date],
'm d_date'[year_week],
'm d_date'[year_month],
FILTER(
'm d_employees',
[Scheduled Hours from daily table] > 0
),
"scheduled hours",
[Scheduled Hours from daily table]
)

 

 

 

 

Then I have these calculated columns:

 

 

 

Index =
VAR CurrentEMPLOYEEID = FiveDaysWorkedInArowPerWeek[employee_id]
VAR CurrentYearWeek = FiveDaysWorkedInArowPerWeek[year_week]
RETURN
RANKX(
FILTER(
ALL(FiveDaysWorkedInArowPerWeek),
FiveDaysWorkedInArowPerWeek[employee_id] = CurrentEMPLOYEEID &&
FiveDaysWorkedInArowPerWeek[year_week] = CurrentYearWeek
),
FiveDaysWorkedInArowPerWeek[date_date],
,
ASC,
Dense
)

 

 

 

This just creates a basic index of all rows per employee and week. I.e. If the first day of the week is monday, they it gets 1, and if the last day is friday but wednesday was off, friday gets 4 as there were only 4 entries for that week.

 

 

 

Consecutive Days Initial =
VAR CurrentEMPLOYEEID = FiveDaysWorkedInArowPerWeek[employee_id]
VAR CurrentYearWeek = FiveDaysWorkedInArowPerWeek[year_week]
VAR CurrentDate = FiveDaysWorkedInArowPerWeek[date_date]
VAR CurrentIndex = FiveDaysWorkedInArowPerWeek[Index]
VAR PreviousDate = CALCULATE(
MAX(FiveDaysWorkedInArowPerWeek[date_date]),
FILTER(
ALLEXCEPT(FiveDaysWorkedInArowPerWeek, FiveDaysWorkedInArowPerWeek[employee_id], FiveDaysWorkedInArowPerWeek[year_week]),
FiveDaysWorkedInArowPerWeek[date_date] < CurrentDate &&
FiveDaysWorkedInArowPerWeek[Index] = CurrentIndex - 1
)
)
VAR DaysDifference = DATEDIFF(PreviousDate, CurrentDate, DAY)
RETURN
IF(
CurrentIndex = 1 || DaysDifference > 1,
1,
0
)

 

 

 

Here the output gives a 1 if the current index is 1, i.e. the first day worked in a week / the first entry in a week, for an employee, and then it gives a 1 if the date is bigger than the previous date by more than 1 day.

I.e:

employee_idyear_weekdate_dateIndexConsecutive days initital
12385742023232023-06-05 (mon)11
12385742023232023-06-06 (tue)20
12385742023232023-06-07 (wed)30
12385742023232023-06-10 (sat)41
12385742023232023-06-11 (sun)50
12385742023242023-06-12 (mon)11
12385742023242023-06-13 (tue)20
12385742023242023-06-14 (wed)30
12385742023242023-06-16 (fri)41
12385742023242023-06-18 (sun)51
12385742023252023-06-24 (sat)11
12385742023262023-06-26 (mon)11
12385742023262023-06-27 (tue)20
12385742023262023-06-28 (wed)30
12385742023262023-06-29 (thu)40

 

 

 

 

Consecutive Days =
VAR CurrentEMPLOYEEID = FiveDaysWorkedInArowPerWeek[employee_id]
VAR CurrentYearWeek = FiveDaysWorkedInArowPerWeek[year_week]
VAR CurrentIndex = FiveDaysWorkedInArowPerWeek[Index]
VAR ConsecutiveStart =
CALCULATE(
MAX(FiveDaysWorkedInArowPerWeek[Index]),
FILTER(
FiveDaysWorkedInArowPerWeek,
FiveDaysWorkedInArowPerWeek[employee_id] = CurrentEMPLOYEEID &&
FiveDaysWorkedInArowPerWeek[year_week] = CurrentYearWeek &&
FiveDaysWorkedInArowPerWeek[Index] <= CurrentIndex &&
FiveDaysWorkedInArowPerWeek[Consecutive Days Initial] = 1
)
)
RETURN
CurrentIndex - ConsecutiveStart + 1

MaxConsecutiveDaysPerWeek =
CALCULATE(
MAX(FiveDaysWorkedInArowPerWeek[Consecutive Days]),
ALLEXCEPT(FiveDaysWorkedInArowPerWeek, FiveDaysWorkedInArowPerWeek[employee_id], FiveDaysWorkedInArowPerWeek[year_week])
)

 

 

 

Finally, here I sort the correct/intended indexing, i.e. it counts from 1 and up within any given week for an employee until it is a new week OR the previous day within a week for an employee is greater than one day apart.

 

I.e.

employee_idyear_weekdate_dateIndexConsecutive days inititalConsecutive days
12385742023232023-06-05 (mon)111
12385742023232023-06-06 (tue)202
12385742023232023-06-07 (wed)303
12385742023232023-06-10 (sat)411
12385742023232023-06-11 (sun)502
12385742023242023-06-12 (mon)111
12385742023242023-06-13 (tue)202
12385742023242023-06-14 (wed)303
12385742023242023-06-16 (fri)411
12385742023242023-06-18 (sun)511
12385742023252023-06-24 (sat)111
12385742023262023-06-26 (mon)111
12385742023262023-06-27 (tue)202
12385742023262023-06-28 (wed)303
12385742023262023-06-29 (thu)404

 

Now that I have the Consecutive Days column, I can go ahead and calculate the percentage of employees (that has any worktime), for each week, that does not exceed 5 (notice I just flip the percentage by subtracting from 1 in the return statement):

 

 

 

PercentageFiveDaysPerWeek =
VAR EmployeesFiveDaysOnly =
CALCULATE(
DISTINCTCOUNT(FiveDaysWorkedInArowPerWeek[employee_id]),
FiveDaysWorkedInArowPerWeek[MaxConsecutiveDaysPerWeek] > 5
)
VAR TotalEmployees = [TotalEmployeesPerWeek]
RETURN
CALCULATE(1-DIVIDE(EmployeesFiveDaysOnly, TotalEmployees, 0))

 

 

 

 

And then I get to my final measure, where I calculate how many employees do not exceed 5 consecutive days per week, each month (as I am presenting per month):

 

 

 

PercentageFiveDaysPerMonth =
AVERAGEX(
SUMMARIZE(
FiveDaysWorkedInArowPerWeek,
FiveDaysWorkedInArowPerWeek[year_month],
"Percentage", [PercentageFiveDaysPerWeek]
),
[Percentage]
)

 

 

 

 

Again, this works fine on its own if I only want to output it in the table using the year_month column from the calculated table called 'FiveDaysWorkedInArowPerWeek'. However, since there are no relationships and they cannot be created due to circular dependencies, I cannot add slicers of any other dimension table on the page, or use the proper year_month column (the one from my 'm d_date' table). So users will not be able to filter on the location dimension connected to the employees table, not on the contract type of the employee, not on the account they belong to, not on any other attributes like WAH/Brick and Mortar, if they are temporary or permanent employees, etc. etc.

I have other measures that work fine, but in this case I could not wrap my head around the complexity of this in order to make it into a series of measures that work dynamically with my dimension tables (i.e. the original employee table and the original date table). Note that the [Scheduled Hours from daily table] measure in the initial table calculation works dynamically wherever it lives as it just sums up time from my schedule table, which has relationships to both the employee and date table.

Anyone have any suggestions?

2 REPLIES 2
v-huijiey-msft
Community Support
Community Support

Hi @Daximillian ,

 

Thanks for the reply from @lbendlin .

 

Have you tried using GroupKind.Local in Power Query as he mentioned?

 

Did it help you solve the problem? If it did, please accept his answer as the solution, which will be of great help to users with similar problems as you.

 

If you have any other questions please feel free to contact me.

 

Best Regards,
Yang
Community Support Team

lbendlin
Super User
Super User

Does this have to be in DAX?  Can be done much simpler in Power Query with GroupKind.Local

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.