DAX Custom 445 Calendar 09-22-2020 17:06 PM

[Greg_Deckler]

Thanks to a request by @dogt1225 from this thread comes this highly configurable DAX Custom 445 Calendar. Now, this is certainly not the first custom calendar nor will it be the last but it is one that I created and I think it is notable because of how easily configurable it is to customize for your own needs. In this instance, the calendar is configured for weeks starting on Saturday and ending on Friday starting on the 5th Saturday of the year 2020 for 2 years.

Additional features of this calendar include assigning a week # of the year, week # of the quarter, sequential week #, quarter, month, day of year, etc.

Custom445 = 
    VAR __StartYear = 2020 // starting year
    VAR __NumYears = 2 // number of years including start year
    VAR __WeekForm = 16 // 16 has Saturday as 1, Friday 7
    VAR __StartDay = 1 // weekday to start calendar on
    VAR __StartWeek = 5 // # instance of weekday to start calendar on (5th Saturday for example)
    VAR __Base = CALENDAR(DATE(__StartYear,1,1),DATE(__StartYear,12,31))
    VAR __StartDate = 
        MAXX(
            FILTER(
                ADDCOLUMNS(
                    __Base,
                    "WeekNum",COUNTROWS(FILTER(__Base,[Date]<=EARLIER([Date]) && WEEKDAY([Date],16) = __StartDay))
                ),
                [WeekNum]=__StartWeek && WEEKDAY([Date],16)=__StartDay
            ),
            [Date]
        )
    VAR __CalendarBase = CALENDAR(__StartDate,__StartDate + 52 * __NumYears * 7 - 1)
    VAR __Calendar = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    ADDCOLUMNS(
                        __CalendarBase,
                        "Year",ROUNDUP(([Date]-__StartDate+1)*1./ (52*7),0)-1+__StartYear,
                        "DAYOFWK#",MOD(([Date] - __StartDate),7)+1,
                        "SEQWK#",COUNTROWS(FILTER(__CalendarBase,[Date]<=EARLIER([Date]) && WEEKDAY([Date],16) = __StartDay)),
                        "DAY#YEAR",MOD(([Date]-__StartDate)*1.,(52*7))+1,
                        "DAY",DAY([Date])
                    ),
                    "WK#",ROUNDUP([DAY#YEAR]/7,0),
                    "QWK#",MOD([SEQWK#]-1,13)+1,
                    "Q",ROUNDUP([DAY#YEAR]/91,0)
                ),
                "Month",SWITCH(TRUE(),
                            [Q]=1 && [QWK#]<=4,1,
                            [Q]=1 && [QWK#]<=8,2,
                            [Q]=1,3,
                            [Q]=2 && [QWK#]<=4,4,
                            [Q]=2 && [QWK#]<=8,5,
                            [Q]=2,6,
                            [Q]=3 && [QWK#]<=4,7,
                            [Q]=3 && [QWK#]<=8,8,
                            [Q]=3,9,
                            [Q]=4 && [QWK#]<=4,10,
                            [Q]=4 && [QWK#]<=8,11,
                            [Q]=4,12
                        )   
            ),
            "MonthName",
                SWITCH([Month],
                    1,"February",
                    2,"March",
                    3,"April",
                    4,"May",
                    5,"June",
                    6,"July",
                    7,"August",
                    8,"September",
                    9,"October",
                    10,"November",
                    11,"December",
                    12,"January"
                )
        )
RETURN
    __Calendar

eyJrIjoiOTE4YTNmOWUtNTBjOS00ZjM3LWEzZjEtMTU3OTE2YjM5ZmFjIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[Hezzlington2024]

@Greg_Deckler Hi Greg, 

Your "year" column presuambly shows calander year, how would I add a Fiscal Year column (i.e. the fiscal year of 2021 is from Feb 2020 - Jan 2021). I need to create the above calender although my start dates can be either July/August and span across 3 Financial Years 

[technolog]

@Greg_Deckler Thanks!

[ericet]

Hi Greg,

Thank you for this. I'm really interested in making it work.

I'm having trouble in fact, I can't figure it out. I need the calendar to start on Aug 30, 2021

but I can't figure out what to do to make it start Aug 30, 2021

Can you point me in the right direction ? 

Thanks in advance

Eric (Montreal, Canada)