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 the...
  • 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