How to take a date range and give them labels (in this case for pay periods)

[ChrisAZ]

Is it possible if we have pay periods that are bi weekly, to create a good way to filter these and select them inside of PowerBI? For example, we have a date range of 1/5 - 1/18, the next one will be 1/19 - 2/1, and it would be great if I could import the data but then have it display as a pay period. Any suggestions?

[Anonymous · Accepted solution]

Hi @ChrisAZ,

Thanks for the reply from Sahir_Maharaj.

Do you want to assign correct period label for each date based on your bi weekly pay period?

If so, here is a simple demo for your reference.

1). Ensure that you have a continuous date table as below:

2). Create a calculated column and write the DAX as below:

Pay Period = 
VAR StartDate = DATE(2025, 1, 1) -- Define the first pay period start date
VAR PeriodLength = 14 -- Length of a pay period in days
VAR DaysSinceStart = DATEDIFF(StartDate, DateTable[Date], DAY) -- Calculate days since the start date
VAR PeriodNumber = INT(DaysSinceStart / PeriodLength) + 1 -- Determine pay period number
RETURN 
    "Pay Period " & PeriodNumber

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Qi

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Sahir_Maharaj]

Hello @ChrisAZ,

Can you please try this approach:

Pay Period Label = 
VAR StartDate = DATE(2025, 1, 5)
VAR DaysSinceStart = DATEDIFF(StartDate, 'Sales'[Date], DAY)
VAR PayPeriodNumber = ROUNDUP(DIVIDE(DaysSinceStart, 14), 0)
RETURN "Pay Period " & PayPeriodNumber

[ChrisAZ]

I am not sure if that quite captures what I need. Maybe I should share my pbix? This is not related to Sales. Also, what is it I would be creating?