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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Custom Pay Periods in Date Table

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

-- 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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

-- 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

Anonymous
Not applicable

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! 

Anonymous
Not applicable

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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

Top Solution Authors