Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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!

  • Anonymous's avatar
    Anonymous
    7 years ago
    -- 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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    -- 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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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.