cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jess11_11
Helper IV
Helper IV

Custom Pay Periods

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. 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Here's a file with how to do this in PQ:

 

https://1drv.ms/u/s!ApyQEauTSLtO6nGYmYQ4K0OFuPrz?e=t0U3yk

 

Bear in mind, though, that you've not given any rule as to what should happen when the calendar ends...

 

Best

Darek

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Here's a file with how to do this in PQ:

 

https://1drv.ms/u/s!ApyQEauTSLtO6nGYmYQ4K0OFuPrz?e=t0U3yk

 

Bear in mind, though, that you've not given any rule as to what should happen when the calendar ends...

 

Best

Darek

View solution in original post

Anonymous
Not applicable

It's of course doable in DAX but why don't you do this where it should really be done - in Power Query?

Best
Darek

Hi @Anonymous, 

 

Sorry i'm still learning Power BI and this is really complex for me to grasp. Could I ask, how would i even do it in Power Query and why? Is it not possible to just manipulate the formula above? I thought this was a DAX issue as i have the base measure already that's returning a custom column?

 

I was using datesbetween function to return 14 days from whichever monday the date falls on but i keep getting an error. Is that the correct function to be using? 

Thanks

Anonymous
Not applicable

No, this should NOT be done in DAX (even if this is possible). Calculated columns do not compress well, first of all, and DAX is a Data Analysis Expression language (for calculations), not a data mashup language. M is a data mashup language.

Best
Darek

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors