Calculate last 3 Weekends Work

[joshua1990]

Hello everybody.

Is it possible to determine, if an employee has worked the last 3 weekends based on this information?

Date	Staff Nr.	Count
02.05.2020	125	1
03.05.2020	125	1

I have a calendar table that contains data for Weekend (Weekday 6 & 7) and Weeknumer (1-53).

In the end I need a column that contains "Yes" or "No", if the employee has worked the last 3 weekends. 

Best Regards

[mahoneypat · Accepted solution]

Here is one way to do it.  Assuming you have a Date table with a relationship to the Date field in your StaffData table.

Worked Last 3 Weekends = var lastthreeweeks = TOPN(3,VALUES('Date'[Week]), CALCULATE(MAX('Date'[Date])))

var weekendsworked = COUNTROWS(FILTER(lastthreeweeks, CALCULATE(COUNTROWS(StaffData), 'Date'[IsWeekend]="Y")))

return weekendsworked

This will return the # of weeks in which at least one weekend day was worked on.  Note two things - this assume the Staff number will be part of your visual (to provide that filter context), and that since technically Sat and Sun are part of two separate weeks in any given weekend, you may not get exactly the results you expect.  I am thinking of a way to add a column on the Date table to group Sat/Sun into the same weekend (probably will need that again).  Hopefully, this gets you close enough and you can modify it for what you need.

Update: This is probably already solved out there somewhere, but here is the calculated column I came up with to group Sat and Sun of each weekend together.  If you use this field instead of Date[Week] in the Values( ) above, you will get what you are looking for.

EndOfWeekendDate = if(WEEKDAY('Date'[Date])=1, 'Date'[Date], 'Date'[Date]+8-WEEKDAY('Date'[Date]))

If this works for you, please mark it as the solution.  Kudos are great too.  Please let me know if it doesn't or if any questions.
Regards,
Pat

[Anonymous · Accepted solution]

// Assumption:
// Calendar connected via Date to T[Date] via 1:*.
// Weeknumber in your Calendar must not restart with
// each year. It must number weeks from the first one
// to the last one. If your Weeknumber restarts in
// each year, please create a column with a continuous
// numbering of weeks and use it instead.

[Worked in Last 3 Weekends] =
var __person = T[Staff Nr]
var __currentWeeknumber = related( 'Calendar'[Weeknumber] )
var __currentDayIsSunday = 
	( related( 'Calendar'[Weekday] ) = 7 )
var __weekendDaysInLast3Weekends =
	filter(
		'Calendar'[Date],
		'Calendar'[Weeknumber] in {
			(__currentWeeknumber - 1) + __currentDayIsSunday,
			(__currentWeeknumber - 2) + __currentDayIsSunday,
			(__currentWeeknumber - 3) + __currentDayIsSunday
		}
		&&
		'Calendar'[Weekday] in {6,7}
	)
var __workedInLast3Weekends =
	COUNTROWS(
		INTERSECT(
			SELECTCOLUMNS(
				FILTER(
					T,
					T[Staff Nr] = __person
				),
				"@Date", T[Date]
			),
			__weekendDaysInLast3Weekends
		)
	) = 6 // working 3 full weekends
return
	if( __workedInLast3Weekends,
		"Yes",
		"No"
	)

Please note that if the day you're calculating this is Sunday, then the logic is different than when you calculate this on a different day. Hence the adjustment in the FILTER function of the form "+ __currentDayIsSunday."

Best

D

[Anonymous]

If your fact table is big, you should not create a calculated column in it because the calculation will be slow, especially if someone gives you a formula that contains CALCULATE. Please do this in Power Query or in the data source.

Best
D

[joshua1990]

Thanks for your advice. The fact table is not really big. How would you calculate that with a dax measure?

[mahoneypat · Accepted solution]

Here is one way to do it.  Assuming you have a Date table with a relationship to the Date field in your StaffData table.

Worked Last 3 Weekends = var lastthreeweeks = TOPN(3,VALUES('Date'[Week]), CALCULATE(MAX('Date'[Date])))

var weekendsworked = COUNTROWS(FILTER(lastthreeweeks, CALCULATE(COUNTROWS(StaffData), 'Date'[IsWeekend]="Y")))

return weekendsworked

This will return the # of weeks in which at least one weekend day was worked on.  Note two things - this assume the Staff number will be part of your visual (to provide that filter context), and that since technically Sat and Sun are part of two separate weeks in any given weekend, you may not get exactly the results you expect.  I am thinking of a way to add a column on the Date table to group Sat/Sun into the same weekend (probably will need that again).  Hopefully, this gets you close enough and you can modify it for what you need.

Update: This is probably already solved out there somewhere, but here is the calculated column I came up with to group Sat and Sun of each weekend together.  If you use this field instead of Date[Week] in the Values( ) above, you will get what you are looking for.

EndOfWeekendDate = if(WEEKDAY('Date'[Date])=1, 'Date'[Date], 'Date'[Date]+8-WEEKDAY('Date'[Date]))

If this works for you, please mark it as the solution.  Kudos are great too.  Please let me know if it doesn't or if any questions.
Regards,
Pat