Forum Discussion

William_Walk's avatar
William_Walk
Frequent Visitor
2 years ago

Creating a week offset that resets every year

Hi

 

Im in the middle of creating a calendar table for a new report and am seeking a way of reporting in variable periods for each year. Is it possible that for the 1st january the period starts and for the 31st December it ends. It would be useful for me for every x number of weeks = 1 period allowing me to change reporting periods as I please. 

 

Thanks

Calendar Table

2 Replies

  • JoeBarry's avatar
    JoeBarry
    Solution Sage
    2 years ago

    Hi William_Walk 

     

    I hope I understood correctly, 

     

    If you want to define date periods with a text or a number column. You can define the date period in Power Query. For example. You want to have a two week periods. 

     

    Add a custom column and add something like this. 

     

    if Date.DayOfYear([Date]) <= 14 then "Period1" else 
if Date.DayOfYear([Date]) > 14 and Date.DayOfYear([Date]) <= 28 then "Period2" else "Period3"

     

    Something along those lines. Using Date.DayOfYear will define this code for every year in your date table

     

    Thanks

    Joe

     

    If this post helps, then please Accept it as the solution