Restart Pay Period Counting After 26th Pay Period

[PatrickLamoste]

Hello!

I have a calendar with the corresponding Pay Period (Biweekly) which I did by using: 

Pay Period Number = FLOOR ( DATEDIFF ( DATE ( 2017,12,23 ), 'Biweekly (Pay Period)'[Date],DAY )/14,1)+1 

But the problem is that after the 26th instance it continues with the counting. I need it to start back at 1 after the 26th period, basically creating a cycle from 1 to 26.

Thank you! Cheers!

Calendar with Pay Period NumberPay Period Last Instance

[AlB · Accepted solution]

Hi @PatrickLamoste

Based on what you already have, try adding the MOD function:

Pay Period Number =
MOD (
    FLOOR (
        DATEDIFF ( DATE ( 2017; 12; 23 ); 'Biweekly (Pay Period)'[Date]; DAY ) / 14;
        1
    );
    26
)
    + 1

Although in this case your data is easy and quick to replicate, it is always best to show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and increases the likelihood of your question being answered.

Cheers