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_id | year_week | date_date | Index | Consecutive days initital |
| 1238574 | 202323 | 2023-06-05 (mon) | 1 | 1 |
| 1238574 | 202323 | 2023-06-06 (tue) | 2 | 0 |
| 1238574 | 202323 | 2023-06-07 (wed) | 3 | 0 |
| 1238574 | 202323 | 2023-06-10 (sat) | 4 | 1 |
| 1238574 | 202323 | 2023-06-11 (sun) | 5 | 0 |
| 1238574 | 202324 | 2023-06-12 (mon) | 1 | 1 |
| 1238574 | 202324 | 2023-06-13 (tue) | 2 | 0 |
| 1238574 | 202324 | 2023-06-14 (wed) | 3 | 0 |
| 1238574 | 202324 | 2023-06-16 (fri) | 4 | 1 |
| 1238574 | 202324 | 2023-06-18 (sun) | 5 | 1 |
| 1238574 | 202325 | 2023-06-24 (sat) | 1 | 1 |
| 1238574 | 202326 | 2023-06-26 (mon) | 1 | 1 |
| 1238574 | 202326 | 2023-06-27 (tue) | 2 | 0 |
| 1238574 | 202326 | 2023-06-28 (wed) | 3 | 0 |
| 1238574 | 202326 | 2023-06-29 (thu) | 4 | 0 |
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_id | year_week | date_date | Index | Consecutive days initital | Consecutive days |
| 1238574 | 202323 | 2023-06-05 (mon) | 1 | 1 | 1 |
| 1238574 | 202323 | 2023-06-06 (tue) | 2 | 0 | 2 |
| 1238574 | 202323 | 2023-06-07 (wed) | 3 | 0 | 3 |
| 1238574 | 202323 | 2023-06-10 (sat) | 4 | 1 | 1 |
| 1238574 | 202323 | 2023-06-11 (sun) | 5 | 0 | 2 |
| 1238574 | 202324 | 2023-06-12 (mon) | 1 | 1 | 1 |
| 1238574 | 202324 | 2023-06-13 (tue) | 2 | 0 | 2 |
| 1238574 | 202324 | 2023-06-14 (wed) | 3 | 0 | 3 |
| 1238574 | 202324 | 2023-06-16 (fri) | 4 | 1 | 1 |
| 1238574 | 202324 | 2023-06-18 (sun) | 5 | 1 | 1 |
| 1238574 | 202325 | 2023-06-24 (sat) | 1 | 1 | 1 |
| 1238574 | 202326 | 2023-06-26 (mon) | 1 | 1 | 1 |
| 1238574 | 202326 | 2023-06-27 (tue) | 2 | 0 | 2 |
| 1238574 | 202326 | 2023-06-28 (wed) | 3 | 0 | 3 |
| 1238574 | 202326 | 2023-06-29 (thu) | 4 | 0 | 4 |
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?
