Calculated pay period help

[LmS2022]

I am working with pay period report and the existing Bi report populated pay period & year as below:

pay period = FLOOR(DATEDIFF(DATE(2019,12,21),'Table'[Date].[DATE],DAY)/14,1)+1

Pay period year =IF (VALUE(FLOOR(DATEDIFF(DATE(2019,12,21),'Table'[Date].[DATE],DAY)/14,1)+1)<27, 2020,2021)

Now, I am working with pay period 1 2022 data, but the problem is the pay period would continue as 28 & the year still is 2021 as shown in the picture.

How can I fix the pay period number start from1 & the year is 2022?

Thanks.

[Anonymous · Accepted solution]

Hi @LmS2022 ,

You can try this code to create a DimDate table then create a relationship between DimDate table and your Data table.

Date = 
VAR _BASIC =
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2019, 12, 21 ), TODAY () ),
        "Year", YEAR ( [Date] ),
        "Pay",
            FLOOR ( DATEDIFF ( DATE ( 2019, 12, 21 ), [Date], DAY ) / 14, 1 ) + 1
    )
VAR _ADD_Pay_Period =
    ADDCOLUMNS (
        _BASIC,
        "Pay Period",
            VAR _MAXPRE =
                MAXX (
                    FILTER ( _BASIC, [Date] = DATE ( EARLIER ( [Year] ) - 1, 12, 31 ) ),
                    [Pay]
                )
            VAR _MAXPRE2 =
                MAXX (
                    FILTER ( _BASIC, [Date] = DATE ( EARLIER ( [Year] ) - 2, 12, 31 ) ),
                    [Pay]
                )
            RETURN
                IF (
                    [Pay] = 1,
                    26,
                    IF ( [Pay] = _MAXPRE, _MAXPRE - _MAXPRE2, [Pay] - _MAXPRE )
                )
    )
VAR _ADD_Key =
    ADDCOLUMNS (
        _ADD_Pay_Period,
        "KEY", COMBINEVALUES ( " ", [Pay], [Pay Period] )
    )
VAR _ADD_Period_Year =
    ADDCOLUMNS (
        _ADD_Key,
        "Period Year", MINX ( FILTER ( _ADD_Key, [KEY] = EARLIER ( [KEY] ) ), [Year] )
    )
VAR _SUMMARIZE =
    SUMMARIZE ( _ADD_Period_Year, [Date], [Year], [Pay Period], [Period Year] )
RETURN
    _SUMMARIZE

DimDate table looks like as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LmS2022 · Accepted solution]

Hello Rico

Thank you very much for your help. I have tried and it looks like there is a error as Token Eof expected on line 2

VAR _BASIC =

Would you please look at?

Thank you very much!!

[Anonymous · Accepted solution]

Hi @LmS2022 ,

You can try this code to create a DimDate table then create a relationship between DimDate table and your Data table.

Date = 
VAR _BASIC =
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2019, 12, 21 ), TODAY () ),
        "Year", YEAR ( [Date] ),
        "Pay",
            FLOOR ( DATEDIFF ( DATE ( 2019, 12, 21 ), [Date], DAY ) / 14, 1 ) + 1
    )
VAR _ADD_Pay_Period =
    ADDCOLUMNS (
        _BASIC,
        "Pay Period",
            VAR _MAXPRE =
                MAXX (
                    FILTER ( _BASIC, [Date] = DATE ( EARLIER ( [Year] ) - 1, 12, 31 ) ),
                    [Pay]
                )
            VAR _MAXPRE2 =
                MAXX (
                    FILTER ( _BASIC, [Date] = DATE ( EARLIER ( [Year] ) - 2, 12, 31 ) ),
                    [Pay]
                )
            RETURN
                IF (
                    [Pay] = 1,
                    26,
                    IF ( [Pay] = _MAXPRE, _MAXPRE - _MAXPRE2, [Pay] - _MAXPRE )
                )
    )
VAR _ADD_Key =
    ADDCOLUMNS (
        _ADD_Pay_Period,
        "KEY", COMBINEVALUES ( " ", [Pay], [Pay Period] )
    )
VAR _ADD_Period_Year =
    ADDCOLUMNS (
        _ADD_Key,
        "Period Year", MINX ( FILTER ( _ADD_Key, [KEY] = EARLIER ( [KEY] ) ), [Year] )
    )
VAR _SUMMARIZE =
    SUMMARIZE ( _ADD_Period_Year, [Date], [Year], [Pay Period], [Period Year] )
RETURN
    _SUMMARIZE

DimDate table looks like as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LmS2022 · Accepted solution]

Hello Rico

Thank you very much for your help. I have tried and it looks like there is a error as Token Eof expected on line 2

VAR _BASIC =

Would you please look at?

Thank you very much!!

[Anonymous · Accepted solution]

Hi @LmS2022 ,

It works well in my sample.

I think you can download my sample file as below and compare it with yours.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LmS2022]

Thank you for your help. There are only 26 pay periods in the table and we have 27 pay period for 2021. How can I fix it? Thank you!

[LmS2022]

Another issue is 12/19/20-1/3/2020 is the real first pay period of 2020 but the table shows 1/4/2020-1/17/2020 as first pay period of 2020. Any suggestion?

Thank you!