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

Daximillian — Fri, 14 Jun 2024 05:23:40 GMT

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?

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

lbendlin — Sat, 15 Jun 2024 22:46:13 GMT

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

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

Anonymous — Wed, 03 Jul 2024 02:10:56 GMT

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

topic Calculate percentage of employees not exceeding 5 consecutive days per week, with measures only in DAX Commands and Tips

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

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

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