topic Re: Custom Pay Periods in Date Table in DAX Commands and Tips

Custom Pay Periods in Date Table

Anonymous — Wed, 24 Jul 2019 04:43:53 GMT

Hi All,

I want to create a custom pay period end date column in my Date table, however, i'm struggling to get it right.

Basically, if a month has 4 Mondays, then the second Monday will be the1st pay period, and the fourth Monday of that month will be the 2nd pay period.

If the month has 5 Mondays, then the 1st pay period will be the first Monday of the month, the second pay period will be the third Monday of the month, and the 3rd pay period will be the fifth Monday of the month.

So there will be 3 pay periods if there are 5 Mondays in the month, and 2 pay periods if there are 4 Mondays in the month.

I found a solution (sort of) on this forum and i tried to play around with it but i can't seem to make it work.

Here is a link of the PBIX file.

Thanks everyone!

Re: Custom Pay Periods in Date Table

Anonymous — Wed, 24 Jul 2019 08:39:26 GMT

-- I can't access the pbix file due to
-- the company policy. Sorry. But here's
-- something that should get you started...

[Pay Period Number] = -- calculated column
var __currentMonth = Dates[MonthNumber] -- month number 1,2,...,12
var __currentYear = Dates[Year] -- obvious
var __currentDate = Dates[Date]
var __numOfMondaysInMonth =
	COUNTROWS(
		filter(
			Dates,
			Dates[MonthNumber] = __currentMonth
			&& Dates[Year] = __currentYear
			&& Dates[DayName] = "Monday" -- you should have this field in your Dates
		)
	)
var __isMonday = ( Dates[DayName] = "Monday" )
var __whichMonday =
	COUNTROWS(
		filter(
			Dates,
			Dates[MonthNumber] = __currentMonth
			&& Dates[Year] = __currentYear
			&& Dates[DayName] = "Monday"
			&& Dates[Date] <= __currentDate
		)
	)
var __result =
	if (
		__isMonday,
		
		SWITCH( TRUE(),
		
			__numOfMondaysInMonth = 4,
				SWITCH( __whichMonday,
					2, "2nd",
					4, "4th"
				) & " Pay Period",
				
			__numOfMondaysInMonth = 5,
				SWITCH( __whichMonday,
					1, "1st",
					2, "2nd",
					5, "5th"
				) & " Pay Period",
			
			"Check out the calendar for errors."
		)
	)
return
	__result

Best

Darek

Re: Custom Pay Periods in Date Table

Anonymous — Wed, 24 Jul 2019 11:28:07 GMT

THANK YOU SO SO SO MUCH Anonymous !

This worked perfectly! Just made some few custom changes to make it more specific to my needs but worked like a charm! Appreciate you taking the time to help! Thanks again!

Re: Custom Pay Periods in Date Table

Anonymous — Fri, 31 Jan 2020 17:54:47 GMT

Is there a way to get "Pay period" for every other Saturday? I would like to create a bi-weekly pay period flag using the same logic but it doesnt seems to be working. Thanks.