DAX Custom 445 Calendar

Greg_Deckler — Wed, 23 Sep 2020 00:06:16 GMT

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

Re: DAX Custom 445 Calendar

ericet — Mon, 17 Jan 2022 03:13:02 GMT

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)

Re: DAX Custom 445 Calendar

technolog — Sun, 25 Feb 2024 13:39:22 GMT

Greg_Deckler Thanks!

Re: DAX Custom 445 Calendar

Hezzlington2024 — Wed, 19 Jun 2024 22:02:43 GMT

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

topic Re: DAX Custom 445 Calendar in Quick Measures Gallery

DAX Custom 445 Calendar

Re: DAX Custom 445 Calendar

Re: DAX Custom 445 Calendar

Re: DAX Custom 445 Calendar