Hi All,
I have something i can't seem to figure out.
I have a dates table with contiguous dates and i need to create a custom column that returns pay periods.
My requirements are:
1. if the month has 4 mondays, then i want to return the 2nd and 4th monday date.
2. if the mont has 5 mondays, then i want to return the 1st, 3rd and 5th monday date.
Someone ( @Anonymous ) on this forum gave me a great solution which i've pasted below and it did the trick, however, now i need return the pay period date for all contiguous dates in the dates column that falls within a range.
[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
What the solution above does is it returns the date of the relevant monday only.
What i want however is the following:
Say a month (i.e. July 2019) has 5 Mondays. This means, i want to return dates 01/07/2019 (1st monday of July), 15/07/2019 (3rd monday of July) & 29/07/2019 (5th monday of July). This is what the above solution does.
Now what i want to do is, for example, for dates between 18th June 2019 to 1st July 2019 (a 2 week period), I want to assign "01/07/19" against it all those dates.
For dates between 2nd July 2019 to 15th July 2019, I want to assign "15/07/2019" against all those dates, and same logic for the 16th July 2019 to 29th July 2019.
I want to apply this same logic for all dates in my dates table so that my custom pay period column has no blank values and all dates have an assigned pay period date assigned to it.
Thanks everyone and sorry for the long post.
