Forum Discussion
Anonymous
7 years agoNot 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 the...
- Anonymous7 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
7 years agoNot 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
6 years agoNot 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.