Date Hierarchy - Splitting my week into two separate years

[jessyssej]

I created a Date Hierarchy to look at Calendar Year, followed by Pay Period (2 weeks or 26 in a year) everything works great! EXCEPT for the split of the pay period that falls under 2 different calendar years.    Any thoughts?

This is what I used for the Pay Period =

Pay Period Number = MOD (

     FLOOR (

        DATEDIFF(DATE(2019,12,30),'Calendar'[Date],DAY)/14,

        1

    ),

    26

) +1

[amitchandak]

@jessyssej , to deal with such case you need to use the period year. not a calendar year. This same problem with week also. Either period start date or period enda= date should decide the period, no the date

[jessyssej]

This sounds interesting, would this be added to my calendar table?  Measure?

[mahoneypat]

Assuming that pay period truly does wrap around between 2019 and 2020, the simplest would be to remove the Year from your hierarchy.  Or you could make a new column in your Calendar table that captures all needed info in a single value (Year and Pay Period).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[jessyssej]

Thanks for your reply.  I don't really want to remove the year, in fact I've been asked to add Fiscal year which will make it that much more complicated.  I thought about creating a column with all the values but I'm not in love with that option yet.  Thanks again!