create calendar table with a span of three years per calendar year

[newtopbitoo]

Hi there,

I have to create some kind of calendar table containing multiple alternative years. 
Each alternative year contains records starting in previous calendar year and ending in next calendar year.
For example in my case alternative year 2023 contains records from august  1 2022 until july 31 2024.

Extra challenge. The table has to contain weeknumbers starting 1 and counting until approximately 99.
Relation to another table will be set by a key like (alternative year)+date

I cannot think of how to realize this

[Anonymous]

Hi @newtopbitoo ,

You can try this code to create a calendar table.

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 08, 01 ), DATE ( 2024, 07, 31 ) ),
    "Alternative Year", 2023,
    "Weekday", FORMAT ( [Date], "DDDD" ),
    "WeekStart",
        [Date] - WEEKDAY ( [Date], 2 ) + 1,
    "Key",
        2023 & " " & [Date]
)

Result is 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.

[newtopbitoo]

Thank you for your answer.
This creates only a calendar for only one year while it has to be created for multiple years. So there must be some kind of loop  in it, like : for year = 2020 tot 2025

EDIT: solved the multiple year problem by creating a table for every year and use the "union "function to concatenate them
Besides that there is still not a weeknumber in ( 1 to 99) in it.
EDIT: solved weeknumbers now to with this formula

int(([Date]-Date(2021,09,26)+7 )/7   (2021-09-26 is different for every year in my case)