link and documentation dim calendar
download txt-file with source code
source code
dim Calendar =
// Definition of the variables
VAR Datum_Start = date(2020;1;1) //min('Table'[Field Date]) //date(2020; 01; 01)
VAR Date_Stop = date(2030;12;31) //max('Table'[Field Date]) //date(2022;12;31)
VAR Date_Today = Today()
VAR FiscalYear_StartMonth = 10 //In which Month start the FiscalYear? / FiscalYear (FY) --> from 01.10. to 30.09. = 10
VAR FiscalYear_Praefix = "FY"
// Build the calendar with the different columns
VAR Result =
ADDCOLUMNS (
// Dates
CALENDAR(Datum_Start; Date_Stop);
"DateUSFormat"; FORMAT ( [Date]; "YYYY-MM-DD" );
"DateAsInteger"; INT ( FORMAT ( [Date]; "YYYYMMDD" ) );
"Day"; INT ( FORMAT ( [Date]; "DD" ) );
// Year
"YearAsInteger"; INT ( FORMAT ([Date]; "YYYY" ) );
"YearDiff"; YEAR([Date]) - YEAR(Date_Today);
// Berechnung Fiscal Year
"FiscalYear";
IF ( FiscalYear_StartMonth = 1 ; FORMAT ([Date]; "YYYY" );
IF ( INT ( FORMAT ( [Date]; "MM" ) ) < FiscalYear_StartMonth;
FiscalYear_Praefix & INT ( FORMAT ([Date]; "YYYY" ) )-1 & "/" & INT ( FORMAT ([Date]; "YY" ) );
FiscalYear_Praefix & INT ( FORMAT ([Date]; "YYYY" ) ) & "/" & INT ( FORMAT ([Date]; "YY" ) )+1
));
"FiscalMonthSort";
IF ( INT ( FORMAT ([Date]; "MM" ) ) < FiscalYear_StartMonth ;
12 - FiscalYear_StartMonth + INT ( FORMAT ([Date]; "MM" ) ) ;
INT ( FORMAT ([Date]; "MM" ) ) - FiscalYear_StartMonth
)+1;
"FiscalYearMonth";
IF ( FiscalYear_StartMonth = 1 ; FORMAT ([Date]; "YYYY" );
IF ( INT ( FORMAT ( [Date]; "MM" ) ) < FiscalYear_StartMonth;
FiscalYear_Praefix & INT ( FORMAT ([Date]; "YYYY" ) )-1 & "/" & INT ( FORMAT ([Date]; "YY" ) );
FiscalYear_Praefix & INT ( FORMAT ([Date]; "YYYY" ) ) & "/" & INT ( FORMAT ([Date]; "YY" ) )+1
)) &
"-" &
FORMAT (
IF ( INT ( FORMAT ([Date]; "MM" ) ) < FiscalYear_StartMonth ;
12 - FiscalYear_StartMonth + INT ( FORMAT ([Date]; "MM" ) ) ;
INT ( FORMAT ([Date]; "MM" ) ) - FiscalYear_StartMonth
)+1; "00");
"FiscalQuarter";
"Q" & ROUNDUP(
(IF ( INT ( FORMAT ([Date]; "MM" ) ) < FiscalYear_StartMonth;
12 - FiscalYear_StartMonth + INT ( FORMAT ([Date]; "MM" ) );
INT ( FORMAT ([Date]; "MM" ) ) - FiscalYear_StartMonth )
+1)
/3;0);
"FiscalYearQuarter";
IF ( FiscalYear_StartMonth = 1 ; FORMAT ([Date]; "YYYY" );
IF ( INT ( FORMAT ( [Date]; "MM" ) ) < FiscalYear_StartMonth;
FiscalYear_Praefix & INT ( FORMAT ([Date]; "YYYY" ) )-1 & "/" & INT ( FORMAT ([Date]; "YY" ) );
FiscalYear_Praefix & INT ( FORMAT ([Date]; "YYYY" ) ) & "/" & INT ( FORMAT ([Date]; "YY" ) )+1
)) &
"-" &
"Q" & ROUNDUP(
(IF ( INT ( FORMAT ([Date]; "MM" ) ) < FiscalYear_StartMonth;
12 - FiscalYear_StartMonth + INT ( FORMAT ([Date]; "MM" ) );
INT ( FORMAT ([Date]; "MM" ) ) - FiscalYear_StartMonth )
+1)
/3;0);
// Quarter
"Quarter"; "Q" & FORMAT([Date]; "Q");
"YearQuarter"; FORMAT([Date]; "YYYY") & "/Q" & FORMAT([Date]; "Q");
"QuarterDiff";
IF ( [Date] < Date_Today;
-1 * DATEDIFF ( [Date]; Date_Today; QUARTER);
DATEDIFF ( Date_Today; [Date]; QUARTER)
);
// Month
"YearMonthOnlyNumber"; FORMAT ( [Date]; "YYYY-MM" );
"YearMonthPlusShortName"; FORMAT ( [Date]; "YYYY-MM MMM" );
"YearMonthPlusLongName"; FORMAT ( [Date]; "YYYY-MM MMMM" );
"MonthNo"; FORMAT ( [Date]; "MM" );
"MonthShortName"; FORMAT ( [Date]; "MMM" );
"MonthLongName"; FORMAT ( [Date]; "MMMM" );
"MonthNoShortName"; FORMAT ( [Date]; "MM MMM" );
"MonthNoLongName"; FORMAT ( [Date]; "MM MMMM" );
"MonthDiff";
IF ( [Date] < Date_Today;
-1 * DATEDIFF ( [Date]; Date_Today; MONTH);
DATEDIFF ( Date_Today; [Date]; MONTH)
);
// Week
//"CalendarWeekMonday"; WEEKNUM ( [Date]; 21 );
//"CalendarWeekSunday"; WEEKNUM ( [Date]+1; 21 );
//"MondayThisWeek"; Date_Today - WEEKDAY(Date_Today;2)+1;
//"SundayThisWeek"; Date_Today - WEEKDAY(Date_Today;1)+1;
"YearCalendarWeekSunday";
If (
AND ( Month([Date])=1; WEEKNUM([Date]+1;21)>47 );
Year([Date])-1;
If ( AND ( Month([Date])=12; WEEKNUM([Date]+1;21)<5 );
YEAR([Date])+1;
YEAR([Date])
)
)
& "/" & FORMAT(WEEKNUM([Date]+1;21);"00");
"YearCalendarWeekMonday";
If (
AND ( Month([Date])=1; WEEKNUM([Date];21)>47 );
Year([Date])-1;
If ( AND ( Month([Date])=12; WEEKNUM([Date];21)<5 );
YEAR([Date])+1;
YEAR([Date])
)
)
& "/" & FORMAT(WEEKNUM([Date];21);"00");
"CalendarWeekDiffSunday";
IF (
[Date] >= (Date_Today-WEEKDAY(Date_Today;1)+1);
ROUNDDOWN(((Date_Today-WEEKDAY(Date_Today;1)+1) - [Date]) / -7; 0);
ROUNDUP(((Date_Today-WEEKDAY(Date_Today;1)+1) - [Date]) / -7; 0)
);
"CalendarWeekDiffMonday";
IF (
[Date] >= (Date_Today-WEEKDAY(Date_Today;2)+1);
ROUNDDOWN(((Date_Today-WEEKDAY(Date_Today;2)+1) - [Date]) / -7; 0);
ROUNDUP(((Date_Today-WEEKDAY(Date_Today;2)+1) - [Date]) / -7; 0)
);
// Day
"WeekdayNo"; INT ( WEEKDAY ( [Date]; 2 ) ); // 2 = Weekday start with 1 on Monday
"WeekdayNameShort"; FORMAT ( [Date]; "DDD" );
"WeekdayNameLong"; FORMAT ( [Date]; "DDDD" );
"DayDiff"; INT ( [Date] - Date_Today )
)
RETURN Result