Bi weekly pay period number

[ypurcaro]

Hello folks,

I can't find any information of this topic on youtube; so, I'm here hoping someone can help me with this problem.

I have a bi weekly payroll calendar that starts on December 21, 2020 to December 21, 2021. So, from the start date to the end date, for every 14 days, there should be a pay period. For example, the pay period 1 start from December 21, 2020 to January 2, 2021 and so forth. This will be my calendar table in power bi. How do we use Dax to identify the pay period based on the calendar?

Thanks for your help.

 

[mrshl9898]

Like this?

P2 = IF(ISODD(WEEKNUM(Sheet1[Date]+20)),WEEKNUM(Sheet1[Date]+20),WEEKNUM(Sheet1[Date]+13))/2-1

 

[ypurcaro]

Like this?

P2 = IF(ISODD(WEEKNUM(Sheet1[Date]+20)),WEEKNUM(Sheet1[Date]+20),WEEKNUM(Sheet1[Date]+13))/2-1

View attachment 37352

Yes, something like this but the number of period is off. It did return 26 pay periods which exactly what I wanted. However, there is a -1 pay period. And the -1 pay period falls on the dates of 12/12/2021 and 12/19/2021. Then, on the pay period #26, it has only 12 days, missing dates 12/12/2021 and 12/19/2021. Since I've never worked on the calendar table like this, I have no clue but I do know we need to use the IF function, that's all I know. I appreciate your help very much.

 

[mrshl9898]

Well if those 2 specific dates break the formula you need something manual.

Otherwise my formula with - 2 at the end to start it at - 1