Payroll Calendar and Pay Period Numbers

[PowerBIFreak]

Hello Folks,

I am having hard time witht this and hoping someone can help me. I'm just learning the Power BI and managed to do some calculations and create a calendar table. However, I'm not sure if there is possible to do something like this in Power BI.

I have a payroll calendar with start date 12/21/2020 and end date 12/19/2021. Within this date range, we count every 14 days as a pay period. For example, 12/21/2020 to 1/3/2021 is pay period 1 (The biggest problem here is we overlapped 2 different years). Do we use IF function for this calculation?

[Jihwan_Kim · Accepted solution]

Hi, @PowerBIFreak 

Please correct me if I wrongly understood your question.

I think, you can easily create the calendar table like below for the first four columns.

In order to create the last column in the below picture, please try the below.

Period Number =

VAR currentdate = 'Calendar'[Date]

RETURN

"Period "

& ROUND (

DIVIDE (

SUMX (

FILTER ( 'Calendar', 'Calendar'[Date] <= currentdate ),

IF ( 'Calendar'[Day of Week] = 1, 1 )

),

2

),

0

)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[PowerBIFreak]

Yes, Yes, Yes! This is perfect and it's exactly what I needed. Thank you very much for your help. I guess I need to get familiar with variable, am I right? I have not been able to sleep for a couple of weeks because of this issue. Thanks again!😊

[amitchandak]

@PowerBIFreak , in such case you have to populate the year start and year end date on all records . Same way period start and end dates and then you deal these things with help of Rank

New columns in date table

Period Rank = RANKX(all('Period'),'Period'[period start date],,ASC,Dense)

Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)

Measures

This Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))

This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))

Same way week deal with weeks

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[PowerBIFreak]

This is way beyond my level! But I still want to try it. On my calendar table, I have only one column, the date column from 12/21/20 to 12/19/21. So, on the Period and Year rank function, after the "all", it should be the column name, am I correct?

Thanks.