Calculated pay period help

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.

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!!

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.