Standard Calendar (Date) table in Power BI
In this article I will try to cover only standard calendar calculations, starting from simple to advanced.
For custom (non-standard) calendars like 4-4-5 there will be another article. Stay tuned!
Date table is also called Lookup table or dimension table (contains unique values)
Why you need a reference Date table? Probably one of the most important dimensions.
Because…The Auto Date/Time feature available in Power BI have some several limitations:
- It has a fixed set of rows e.g., Year > Quarter > Month > Day time hierarchy.
- It does not handle fiscal years.
- It does not include weeks.
- It cannot be shared across different tables in the same data model.
Why it is important to your data model?
- The main reason is that you can use it as a time reference point filtering more than one fact table in data model.
- Used as a slicer in report (avoid using date columns in fact / transactions table)
- Used by Time Intelligence functions and don’t have missing dates
In Power BI, basically there are two ways to do that, with Power Query or with DAX. Calendar table is a small and from performance point of view this is not a big problem how you create it (PQ or DAX). Of course, you can use date table already created in SQL or any other ETL tool.
Once you’ve created a calendar table, you are ready to create your first Time Intelligence measures.
For that purpose, keep these requirements in mind for Time Intelligence functions to work properly. This can be achieved using custom calendar table:
- Must have a column of data type Date or Date/time – known as the date column
- The date column must contain unique values
- The date column must not contain BLANKs
- The date column must not have any missing dates
- The date column must span full years. Where a year isn’t necessarily a calendar year (January-December) and cover all dates from the earliest to the last date present in your dataset.
The date table must be marked as a date table
CALENDAR TABLE IN DAX
1. Creating DAX calendar table with static Start & End dates
This is the starting point.
Date =
CALENDAR(
DATE(2018,1,1), --Set Start date here
DATE(2022,12,31)) --Set End date here
In your report, in the Modeling tab at the top of the Power BI window, select New table.
A new blank table will generate. Now paste the DAX code above.
Now you have the base starting column listing all dates (no missing) in the period you specified.
Format the column to fit your needs using the Formatting section in the Modeling tab.
Next, add columns to provide information and categories to the date range appropriate for your anticipated needs.
This is possible by integrating the ADDCOLUMNS function
Here are the most common columns you will use.
You may try adding Year, Quarter, Month and other combinations one by one following the below code or just copy and paste the complete code available down below to practice and tweak it.
“Year”, YEAR ( [Date] ) result is number 2018
“Quarter”, “Q” & FORMAT ( [Date], “Q” ) result is Q1, Q2, Q3, Q4
You can also try some other variations like:
“Quarter”, Q” & ROUNDUP(MONTH([Date])/3,0)
“Quarter”, Q” & QUARTER([Date])
“YearMonth” , YEAR ( [Date]) * 100 + MONTH ( [Date]) – result 201801
“MonthNameLong”, FORMAT ( [Date], “mmmm” ) result is January, February etc.
The expected output of each column is seen below
Here is the complete code you can directly paste in your formula bar:
Date =
ADDCOLUMNS(
CALENDAR (
DATE(2018,1,1), --Set Start date here
DATE(2022,12,31) ), --Set End date here
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date],2 ), --by default starts on Sun-Sat, 2-Mon-Sun
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ) )
Note: This code is using static Start & End Dates. They can be dynamic based on your business requirements. You will find some examples below with dynamic calendar range.
Some functions like WEEKDAY have optional parameters defining start of the week for example. In the code here I’m using parameter 2 where week is from Mon to Sun (1-7)
The below example is showing how the parameters can be defined:
1. Creating DAX calendar table with dynamic Start & End dates
The below code will use Start & End dates from one table or tables in your data model. In this case we will take first sales date and last sales date. First & Last sales dates can come from one table or from different tables (e.g., Actual sales date as MIN & Forecast sales date as MAX, as forecast data can contain future periods, and they will be visualized later when using the calendar dimension on X-Axis in visuals)
Date =
ADDCOLUMNS(
CALENDAR(
MIN(Sales[Date]), --Set Start Date here from actuals table
MAX(Forecast[Date]) --Set End Date here from forecast table
),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date],2 ), --by default Sun-Sat, 2-Mon-Sun
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ) )
This is coming from one table and generating MIN & MAX dates presented in the table:
MIN(Sales[Date]), –Set Start Date here from actuals table
MAX(Sales[Date]) –Set End Date here from actuals table
),
Similar result can be achieved using CALENDARAUTO () function, but MinDate will be start of the year January 1 & MaxDate December 31 end of the year, even though MinDate and MaxDate are different. This is valid if using default parameter.
CALENDARAUTO () function returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model.
In this example, the MinDate and MaxDate in the data model are July 1, 2010 and June 30, 2011.
CALENDARAUTO () will return all dates between January 1, 2010 and December 31, 2011.
You can also generate a date range based on providing a value for fiscal year end month in CALENDARAUTO () function. In this case fiscal year end month is May 31 and fiscal year will start on June 1.
CALENDARAUTO (5) will return all dates between June 1, 2010 and May 31, 2012.
Date =
ADDCOLUMNS (
CALENDARAUTO(),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date],2 ), --by default Sun-Sat, 2-Mon-Sun
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ) )
Remarks for CALENDARAUTO () function
- The date range is calculated as follows:
- The earliest date in the model which is not in a calculated column or calculated table is taken as the MinDate.
- The latest date in the model which is not in a calculated column or calculated table is taken as the MaxDate.
- The date range returned is dates between the beginning of the fiscal year associated with MinDate and the end of the fiscal year associated with MaxDate.
Tip: End date can be also current date (today)
Date =
ADDCOLUMNS(
CALENDAR(
MIN(Sales[Date]), --Set Start date here
TODAY() ), --Set End date here
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date],2 ), --by default Sun-Sat, 2-Mon-Sun
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ) )
ULTIMATE DYNAMIC DAX CALENDAR + FISCAL PERIODS
Date =
VAR _StartDate = MIN(Sales[Date]) --Set Start date
VAR _EndDate = MAX(Forecast[Date]) --Set End date. Current date tip: TODAY()
VAR _startOfFiscalYear = 6 // set the month number that is start of the financial year. example: if fiscal year start is June 1st, value is 6
VAR _WeekStartsOn = "Mon"
VAR _today = TODAY()
RETURN
ADDCOLUMNS(
CALENDAR( _StartDate, _EndDate),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"YearMonth", YEAR ( [Date]) * 100 + MONTH ( [Date]), -- result 201801 -> 201812
"Year", YEAR([Date]),
"Start of Year", DATE( YEAR([Date]),1,1),
"End of Year", DATE( YEAR([Date]),12,31),
"Month", MONTH([Date]), -- 1 to 12
"Start of Month", DATE( YEAR([Date]), MONTH([Date]), 1),
"End of Month", EOMONTH([Date],0),
"Days in Month", DATEDIFF(DATE( YEAR([Date]), MONTH([Date]), 1),EOMONTH([Date],0),DAY)+1,
"Year Month Number", INT(FORMAT([Date],"YYYYMM")),
"Year Month Name", FORMAT([Date],"YYYY-MMM"),
"Day", DAY([Date]),
"Day Name", FORMAT([Date],"DDDD"),
"Day Name Short", FORMAT([Date],"DDD"),
"Day of Week", WEEKDAY([Date],2),
"Day of Year", DATEDIFF(DATE( YEAR([Date]), 1, 1),[Date],DAY)+1, "Month Name", FORMAT([Date],"MMMM"),
"Month Name Short", FORMAT([Date],"MMM"),"Quarter", QUARTER([Date]),
"Quarter Name","Q"& FORMAT([Date],"Q"),
"Year Quarter Number", INT(FORMAT([Date],"YYYYQ")),
"Year Quarter Name", FORMAT([Date],"YYYY")&" Q"&FORMAT([Date],"Q"),
"Start of Quarter", DATE( YEAR([Date]), (QUARTER([Date])*3)-2, 1),
"End of Quarter", EOMONTH(DATE( YEAR([Date]), QUARTER([Date])*3, 1),0),
"Week of Year", WEEKNUM([Date],2),"Start of Week", [Date]-WEEKDAY([Date],2)+1,
"End of Week", [Date]+7-WEEKDAY([Date],2),
"Fiscal Year", IF(_startOfFiscalYear=1,YEAR([Date]),YEAR([Date])+ QUOTIENT(MONTH([Date])+ (13-_startOfFiscalYear),13)),
"Fiscal Year v2", YEAR(EDATE([Date], 13-_startOfFiscalYear)),
"Fiscal Month", MOD( MONTH([Date])+ (13-_startOfFiscalYear) -1 ,12) +1,
"Fiscal Month v2", MONTH(EDATE([Date], 13-_startOfFiscalYear)),
"Fiscal Quarter", QUARTER( DATE( YEAR([Date]),MOD( MONTH([Date])+ (13-_startOfFiscalYear) -1 ,12) +1,1)),
"Fiscal Quarter v2", QUARTER(EDATE([Date], 13-_startOfFiscalYear)),
"Fiscal Date Shift", DATE(YEAR([Date]), MONTH([Date])+ (13-_startOfFiscalYear), DAY([Date])), -- or +7 = Starts on 1st of June (31 May End of Month) shift +/- N months. This can be base to extract Fiscal Year/Month/Week/Day for example
"Fiscal Week v2", WEEKNUM(EDATE([Date], 13-_startOfFiscalYear)),
"Fiscal Week by Goodly",
VAR FiscalFirstDay =
IF (
MONTH ( [Date] ) < _startOfFiscalYear,
DATE ( YEAR ( [Date] ) - 1, _startOfFiscalYear, 1 ),
DATE ( YEAR ( [Date] ), _startOfFiscalYear, 1 )
)
VAR FilteredTableCount =
COUNTROWS (
FILTER (
SELECTCOLUMNS ( GENERATESERIES ( FiscalFirstDay, [Date] ), "Dates", [Value] ),
FORMAT ( [Dates], "ddd" ) = _WeekStartsOn
)
)
VAR WeekNo =
IF (
FORMAT ( FiscalFirstDay, "ddd" ) <> _WeekStartsOn,
FilteredTableCount + 1,
FilteredTableCount )
RETURN WeekNo,
--"Week" & WeekNo,
"Day Offset", DATEDIFF(_today,[Date],DAY),
"Month Offset", DATEDIFF(_today,[Date],MONTH),
"Quarter Offset", DATEDIFF(_today,[Date],QUARTER),
"Year Offset", DATEDIFF(_today,[Date],YEAR),
"Slicer Current Date", IF([Date] = TODAY(), "Today", IF( [Date] < TODAY(), [Date] & "")),
"Slicer Current Month", IF(MONTH([Date]) = MONTH(TODAY()) && YEAR([Date]) = YEAR(TODAY()),
"Current Month", IF(MONTH([Date]) < MONTH(TODAY()) && YEAR([Date]) < YEAR(TODAY()), [Date] & "")))
CALENDAR TABLE IN POWER QUERY (PQ)
Creating PQ calendar table with Start & End dates using Parameters
Go to Power Query Editor (PQ)
PQ can be opened from Home tab then Transform data button
Add Parameters
You will start by creating a parameter for both Start and End dates. To add these, open Power Query and go to Manage Parameters. Then click on New and add the following parameters:
- Name: StartDate. Type: Date, Current Value: 01-01-2018 or 01/01/2018
- Name: EndDate, Type: Date, Current Value: 31-12-2022 or 31/12/2022
Create a List of Dates
Create Blank Query
Paste the code in formula bar and convert To Table
= List.Dates( StartDate, Duration.Days( EndDate - StartDate ) + 1, // Number of steps #duration(1, 0, 0, 0)
)
You can rename Query1 to Date as well (right click Rename)
Format as Date
Rename Column1 as Date (double click on yellow field)
Adding columns for Year, Quarter, Month, Day etc.
Navigate to the Add Column tab (1) and make sure the Date column is selected. Then click on Date icon (2).
The button will only be available when working with a Date or DateTime as a column. After clicking, the below dropdown provides you with several options.
let
Source = List.Dates(
StartDate,
Duration.Days( EndDate - StartDate ) + 1, // Number of steps
#duration(1, 0, 0, 0)
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Inserted Week of Month" = Table.AddColumn(#"Inserted Quarter", "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Week of Month", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Inserted Day" = Table.AddColumn(#"Inserted Day Name", "Day", each Date.Day([Date]), Int64.Type)
in
#"Inserted Day"
Let’s tweak a little bit and make dynamic EndDate
let
//Set your variables here
CurrentDate = DateTime.LocalNow(),
StartDate = #date(2018,1,1), //this is Static StartDate
//EndDate = #date(2022,12,31), //this is Static EndDate
EndDate = #date(Date.Year(CurrentDate),12,31), //this is Dynamic EndDate i.e., End of current year
// Dynamic MIN & MAX dates from data model //comment out the below if you need this case
//StartDate = List.Min(Actual[Date]), //Min Date from Actual table
//EndDate = List.Max(Forecast[Date]), //Max Date can come from Actual table or Forecast table with future dates
Source = List.Dates(
StartDate,
Duration.Days( EndDate - StartDate ) + 1, // Number of steps
#duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Inserted Week of Month" = Table.AddColumn(#"Inserted Quarter", "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Week of Month", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Inserted Day" = Table.AddColumn(#"Inserted Day Name", "Day", each Date.Day([Date]), Int64.Type)
in
#"Inserted Day"
More advanced calendar with Parameters
Just paste it and make sure you’ve created the Parameters steps above
let
Source = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(
Source,
Splitter.SplitByNothing(),
{"Dates"},
null,
ExtraValues.Error
),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table", {{"Dates", type date}}),
#"Insert Age" = Table.AddColumn(
#"Changed Type",
"Age",
each Date.From(DateTime.LocalNow()) - [Dates],
type duration
),
#"Insert Year" = Table.AddColumn(#"Insert Age", "Year", each Date.Year([Dates]), Int64.Type),
#"Insert Start of Year" = Table.AddColumn(
#"Insert Year",
"Start of Year",
each Date.StartOfYear([Dates]),
type date
),
#"Insert End of Year" = Table.AddColumn(
#"Insert Start of Year",
"End of Year",
each Date.EndOfYear([Dates]),
type date
),
#"Insert YYYY-MM" = Table.AddColumn(
#"Insert End of Year",
"YYYY-MM",
each Date.ToText([Dates], "yyyy-MM"),
type text
),
#"Insert MonthSh-Year" = Table.AddColumn(
#"Insert YYYY-MM",
"Month-Year",
each Date.ToText([Dates], "MMM yyyy"),
type text
),
#"Insert YearMonth" = Table.AddColumn(
#"Insert MonthSh-Year",
"YearMonth",
each Date.ToText([Dates], "yyyyMM"),
type text
),
#"Insert Month" = Table.AddColumn(
#"Insert YearMonth",
"Month",
each Date.Month([Dates]),
Int64.Type
),
#"Insert Start of Month" = Table.AddColumn(
#"Insert Month",
"Start of Month",
each Date.StartOfMonth([Dates]),
type date
),
#"Insert Days in Month" = Table.AddColumn(
#"Insert Start of Month",
"Days in Month",
each Date.DaysInMonth([Dates]),
Int64.Type
),
#"Insert Month Name" = Table.AddColumn(
#"Insert Days in Month",
"Month Name",
each Date.MonthName([Dates]),
type text
),
#"Insert Month Name Short" = Table.AddColumn(
#"Insert Month Name",
"Month Name Short",
each Date.ToText([Dates], "MMM"),
type text
),
#"Insert Month Name Short NL" = Table.AddColumn(
#"Insert Month Name Short",
"Month Name Short (NL)",
each Date.ToText([Dates], "MMM", "nl-NL"),
type text
),
#"Insert Quarter Number" = Table.AddColumn(
#"Insert Month Name Short NL",
"Quarter Number",
each Date.QuarterOfYear([Dates]),
Int64.Type
),
#"Insert Start of Quarter" = Table.AddColumn(
#"Insert Quarter Number",
"Start of Quarter",
each Date.StartOfQuarter([Dates]),
type date
),
#"Added Quarter" = Table.AddColumn(
#"Insert Start of Quarter",
"Quarter",
each "Q" & Text.From(Date.QuarterOfYear([Dates])),
type text
),
#"Add Year-Quarter" = Table.AddColumn(
#"Added Quarter",
"Year-Quarter",
each Text.From(Date.Year([Dates])) & "-Q" & Text.From(Date.QuarterOfYear([Dates])),
type text
),
#"Insert Week of Year" = Table.AddColumn(
#"Add Year-Quarter",
"Week of Year",
each Date.WeekOfYear([Dates]),
Int64.Type
),
#"Insert Week of Month" = Table.AddColumn(
#"Insert Week of Year",
"Week of Month",
each Date.WeekOfMonth([Dates]),
Int64.Type
),
#"Insert Start of Week" = Table.AddColumn(
#"Insert Week of Month",
"Start of Week",
each Date.StartOfWeek([Dates]),
type date
),
#"Insert End of Week" = Table.AddColumn(
#"Insert Start of Week",
"End of Week",
each Date.EndOfWeek([Dates]),
type date
),
#"Insert Day" = Table.AddColumn(#"Insert End of Week", "Day", each Date.Day([Dates]), Int64.Type),
#"Insert Day of Week" = Table.AddColumn(
#"Insert Day",
"Day of Week",
each Date.DayOfWeek([Dates]),
Int64.Type
),
#"Insert Day of Year" = Table.AddColumn(
#"Insert Day of Week",
"Day of Year",
each Date.DayOfYear([Dates]),
Int64.Type
),
#"Insert Start of Day" = Table.AddColumn(
#"Insert Day of Year",
"Start of Day",
each Date.StartOfDay([Dates]),
type date
),
#"Insert End of Day" = Table.AddColumn(
#"Insert Start of Day",
"End of Day",
each Date.EndOfDay([Dates]),
type date
),
#"Insert Day Name" = Table.AddColumn(
#"Insert End of Day",
"Day Name",
each Date.DayOfWeekName([Dates]),
type text
),
#"Insert Is Weekend" = Table.AddColumn(
#"Insert Day Name",
"Is Weekend",
each if Date.DayOfWeek([Dates]) >= 5 then 1 else 0,
Int64.Type
),
#"Insert Is Weekday" = Table.AddColumn(
#"Insert Is Weekend",
"Is Weekday",
each if Date.DayOfWeek([Dates]) < 5 then 1 else 0,
Int64.Type
)
in
#"Insert Is Weekday"
ULTIMATE DYNAMIC POWER QUERY CALENDAR + FISCAL PERIODS V.1
//********************************* code begins *********************************//
//Comment out (enable) the first line (fnDateTable) if you want to work with Parameters function and at the end after “in ReorderColumns“ then disable the code in Set your variables here below.
//let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonthNum as number, optional Holidays as list, optional WDStartNum as number ) as table =>
let
//Set your variables here
StartDate = List.Min(Actual[Date]), //Min Date from Actual table
EndDate = List.Max(Forecast[Date]), //Max Date can come from Actual table or Forecast table with future dates
FYStartMonthNum = 6, //6 – Starts on June 1 (year-end is May 31)
Holidays = null,
WDStartNum = 1, //1 = Mon
// Date table code starts here
FYStartMonth = if List.Contains( {1..12}, FYStartMonthNum ) then FYStartMonthNum else 1,
StartOfWeekDayName = Text.Proper( Text.Start( Date.DayOfWeekName( #date(2021, 2,1) ), 3)),
WDStart = if List.Contains( {0, 1}, WDStartNum ) then WDStartNum else 0,
CurrentDate = Date.From(DateTime.FixedLocalNow()),
DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
AddToday = if EndDate < CurrentDate then List.Combine( {Source, {CurrentDate}}) else Source,
TableFromList = Table.FromList(AddToday, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), type number),
InsertYearOffset = Table.AddColumn(InsertYear, "YearOffset", each Date.Year([Date]) - Date.Year(Date.From(CurrentDate)), type number),
InsertCompletedYear = Table.AddColumn(InsertYearOffset, "YearCompleted", each Date.EndOfYear([Date]) < Date.From(Date.EndOfYear(CurrentDate)), type logical),
InsertQuarter = Table.AddColumn(InsertCompletedYear, "QuarterOfYear", each Date.QuarterOfYear([Date]), type number),
InsertCalendarQtr = Table.AddColumn(InsertQuarter, "Quarter & Year", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year]), type text),
InsertQuarternYear = Table.AddColumn(InsertCalendarQtr, "QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100, type number),
InsertQuarterOffset = Table.AddColumn(InsertQuarternYear, "QuarterOffset", each ((4 * Date.Year([Date])) + Date.QuarterOfYear([Date])) - ((4 * Date.Year(Date.From(CurrentDate))) + Date.QuarterOfYear(Date.From(CurrentDate))), type number),
InsertCompletedQuarter = Table.AddColumn(InsertQuarterOffset, "QuarterCompleted", each Date.EndOfQuarter([Date]) < Date.From(Date.EndOfQuarter(CurrentDate)), type logical),
InsertMonth = Table.AddColumn(InsertCompletedQuarter, "MonthOfYear", each Date.Month([Date]), type number),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date]), type number),
InsertMonthName = Table.AddColumn(InsertDay, "Month Name", each Text.Proper( Date.ToText([Date], "MMMM")), type text),
InsertMonthShort = Table.AddColumn( InsertMonthName, "MonthShortName", each try Text.Proper( Text.Start([Month Name], 3 )) otherwise Text.Proper( [Month Name] ), type text),
InsertMonthInitial = Table.AddColumn(InsertMonthShort, "Month Initial", each Text.Proper(Text.Start([Month Name], 1)) & Text.Repeat( Character.FromNumber(8203), [MonthOfYear] ), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthInitial, "Month & Year", each [MonthShortName] & " " & Number.ToText([Year]), type text),
InsertMonthnYear = Table.AddColumn(InsertCalendarMonth , "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100, type number),
InsertMonthOffset = Table.AddColumn(InsertMonthnYear, "MonthOffset", each ((12 * Date.Year([Date])) + Date.Month([Date])) - ((12 * Date.Year(Date.From(CurrentDate))) + Date.Month(Date.From(CurrentDate))), type number),
InsertCompletedMonth = Table.AddColumn(InsertMonthOffset, "MonthCompleted", each Date.EndOfMonth([Date]) < Date.From(Date.EndOfMonth(CurrentDate)), type logical),
InsertMonthEnding = Table.AddColumn(InsertCompletedMonth, "MonthEnding", each Date.EndOfMonth([Date]), type date),
InsertDayInt = Table.AddColumn(InsertMonthEnding, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth], type number),
InsertDayOfYear = Table.AddColumn(InsertDayInt, "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
InsertDayWeek = Table.AddColumn(InsertDayOfYear, "DayOfWeek", each Date.DayOfWeek([Date], Day.Monday) + WDStart, Int64.Type),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Text.Proper( Date.ToText([Date], "dddd" )), type text),
InsertDayInitial = Table.AddColumn(InsertDayName, "Weekday Initial", each Text.Proper(Text.Start([DayOfWeekName], 1)) & Text.Repeat( Character.FromNumber(8203), [DayOfWeek] ), type text),
InsertWeekNumber= Table.AddColumn(InsertDayInitial, "ISO Weeknumber", each
if Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0
then Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7)
else if (Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4))
then 1 else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7), type number),
InsertISOyear = Table.AddColumn(InsertWeekNumber, "ISO Year", each Date.Year( Date.AddDays( Date.StartOfWeek([Date], Day.Monday), 3 )), Int64.Type),
BufferTable = Table.Buffer(Table.Distinct( InsertISOyear[[ISO Year], [DateInt]])),
InsertISOqNum = Table.AddColumn(InsertISOyear, "ISO QuarterOfYear", each if [ISO Weeknumber] >39 then 4 else if [ISO Weeknumber] >26 then 3 else if [ISO Weeknumber] >13 then 2 else 1, Int64.Type),
InsertISOqtr = Table.AddColumn(InsertISOqNum, "ISO Quarter", each "Q" & Number.ToText([ISO QuarterOfYear]), type text),
InsertISOQuarter = Table.AddColumn(InsertISOqtr, "ISO Quarter & Year", each "Q" & Number.ToText([ISO QuarterOfYear]) & " " & Number.ToText([ISO Year]), type text),
InsertISOqNy = Table.AddColumn(InsertISOQuarter, "ISO QuarternYear", each [ISO Year] * 10000 + [ISO QuarterOfYear] * 100, type number),
//InsertISOday = Table.AddColumn(InsertISOqNy, "ISO Day of Year", (OT) => Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[DateInt] <= OT[DateInt] and IT[ISO Year] = OT[ISO Year])), Int64.Type),
InsertCalendarWk = Table.AddColumn(InsertISOqNy, "Week & Year", each Text.From([ISO Year]) & "-" & Text.PadStart( Text.From( [ISO Weeknumber] ), 2, "0"), type text ),
InsertWeeknYear = Table.AddColumn(InsertCalendarWk, "WeeknYear", each [ISO Year] * 10000 + [ISO Weeknumber] * 100, Int64.Type),
InsertWeekOffset = Table.AddColumn(InsertWeeknYear, "WeekOffset", each (Number.From(Date.StartOfWeek([Date], Day.Monday))-Number.From(Date.StartOfWeek(CurrentDate, Day.Monday)))/7, type number),
InsertCompletedWeek = Table.AddColumn(InsertWeekOffset, "WeekCompleted", each Date.EndOfWeek( [Date], Day.Monday) < Date.From(Date.EndOfWeek(CurrentDate, Day.Monday)), type logical),
InsertWeekEnding = Table.AddColumn(InsertCompletedWeek, "WeekEnding", each Date.EndOfWeek( [Date], Day.Monday), type date),
AddFY = Table.AddColumn(InsertWeekEnding, "Fiscal Year", each "FY" & (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then Text.PadEnd( Text.End( Text.From([Year] +1), 2), 2, "0") else Text.End( Text.From([Year]), 2)), type text),
AddFQ = Table.AddColumn(AddFY, "Fiscal Quarter", each "FQ" & Text.From( Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 )), type text),
AddFQnYr = Table.AddColumn(AddFQ, "FQuarternYear", each (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then [Year] +1 else [Year]) * 10000 + Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 ) * 100, type number),
AddFM = Table.AddColumn(AddFQnYr, "Fiscal Period", each if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then [MonthOfYear] - (FYStartMonth-1) else if [MonthOfYear] >= FYStartMonth and FYStartMonth =1 then [MonthOfYear] else [MonthOfYear] + (12-FYStartMonth+1), type text),
AddFMnYr = Table.AddColumn(AddFM , "FPeriodnYear", each (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then [Year] +1 else [Year]) * 10000 + [Fiscal Period] * 100, type number),
FYCalendarStart = #date( Date.Year(StartDate)-1, FYStartMonth, 1 ),
InsertFFD = Table.AddColumn( AddFMnYr, "FiscalFirstDay", each if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then #date( Date.Year([Date])+1, FYStartMonth, 1) else #date( Date.Year([Date]), FYStartMonth, 1) ),
AddFYDateRange = Table.Buffer( Table.ExpandTableColumn( Table.ExpandTableColumn( Table.AddColumn( Table.Group( Table.Group( Table.AddColumn( Table.AddColumn(
Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( { Number.From(FYCalendarStart)..Number.From(EndDate) }, Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "Date"}}),
"FiscalFirstDay", each if Date.Month([Date]) < FYStartMonth then #date( Date.Year([Date]), FYStartMonth, 1) else #date( Date.Year([Date])+1, FYStartMonth, 1)),
"FWStartDate", each Date.AddYears(Date.StartOfWeek( [Date], Day.Monday), 1)),
{"FiscalFirstDay", "FWStartDate"}, {{"AllRows", each _, type table [Date=nullable date, FiscalFirstDay=date, FWStartDate=date]}}),
{"FiscalFirstDay"}, {{"AllRows2", each _, type table [FiscalFirstDay=date, FWStartDate=date, AllRows=table]}}),
"Custom", each Table.AddIndexColumn( [AllRows2], "FY Week", 1, 1))[[Custom]],
"Custom", {"FiscalFirstDay", "FWStartDate", "AllRows", "FY Week"}, {"FiscalFirstDay", "FWStartDate", "AllRows", "FY Week"}),
"AllRows", {"Date"}, {"Date"})[[Date], [FY Week]]
),
MergeFYW = Table.NestedJoin(InsertFFD, {"Date"}, AddFYDateRange, {"Date"}, "AddFYWeek", JoinKind.LeftOuter),
ExpandFYWeek = Table.TransformColumnTypes( Table.ExpandTableColumn(MergeFYW, "AddFYWeek", {"FY Week"}, {"Fiscal Week"}),{{"Fiscal Week", Int64.Type}}),
AddFYW = Table.AddColumn( ExpandFYWeek, "Fiscal Year & Week", each if FYStartMonth =1 then [#"Week & Year"] else if Date.Month([Date]) < FYStartMonth then Text.From( Date.Year([Date])) & "-" & Text.PadStart( Text.From([Fiscal Week]), 2, "0") else Text.From( Date.Year([Date])+1) & "-" & Text.PadStart(Text.From([Fiscal Week]), 2, "0"), type text),
InsertFWeeknYear = Table.AddColumn(AddFYW, "FWeeknYear", each if FYStartMonth =1 then [WeeknYear] else (if Date.Month([Date]) < FYStartMonth then Date.Year([Date]) else Date.Year([Date])+1) * 10000 + [Fiscal Week] * 100, Int64.Type),
InsertIsAfterToday = Table.AddColumn(InsertFWeeknYear, "IsAfterToday", each not ([Date] <= Date.From(CurrentDate)), type logical),
InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWorkingDay", each if Date.DayOfWeek([Date], Day.Monday) > 4 then false else true, type logical),
InsertIsHoliday = Table.AddColumn(InsertIsWorkingDay, "IsHoliday", each if Holidays = null then "Unknown" else List.Contains( Holidays, [Date] ), if Holidays = null then type text else type logical),
InsertIsBusinessDay = Table.AddColumn(InsertIsHoliday, "IsBusinessDay", each if [IsWorkingDay] = true and [IsHoliday] <> true then true else false, type logical),
InsertDayType = Table.AddColumn(InsertIsBusinessDay, "Day Type", each if [IsHoliday] = true then "Holiday" else if [IsWorkingDay] = false then "Weekend" else if [IsWorkingDay] = true then "Weekday" else null, type text),
CurrentDateRecord = Table.SelectRows(InsertDayType, each ([Date] = CurrentDate)),
CurrentISOyear = CurrentDateRecord{0}[ISO Year],
CurrentISOqtr = CurrentDateRecord{0}[ISO QuarterOfYear],
CurrentYear = CurrentDateRecord{0}[Year],
CurrentMonth = CurrentDateRecord{0}[MonthOfYear],
CurrentFiscalFirstDay = CurrentDateRecord{0}[FiscalFirstDay],
PrevFiscalFirstDay = Date.AddYears(CurrentFiscalFirstDay, -1),
CurrentFQ = CurrentDateRecord{0}[FQuarternYear],
CurrentFP = CurrentDateRecord{0}[FPeriodnYear],
CurrentFW = CurrentDateRecord{0}[FWeeknYear],
InsertISOQtrOffset = Table.AddColumn(InsertDayType, "ISO QuarterOffset", each ((4 * [ISO Year]) + [ISO QuarterOfYear]) - ((4 * CurrentISOyear) + CurrentISOqtr), type number),
InsertISOYrOffset = Table.AddColumn(InsertISOQtrOffset, "ISO YearOffset", each [ISO Year] - CurrentISOyear, type number),
InsertFYoffset = Table.AddColumn(InsertISOYrOffset, "FiscalYearOffset", each try (if [MonthOfYear] >= FYStartMonth then [Year]+1 else [Year]) - (if CurrentMonth >= FYStartMonth then CurrentYear+1 else CurrentYear) otherwise null, type number),
InsertCurrentFQ = Table.AddColumn(InsertFYoffset, "IsCurrentFQ", each if [FQuarternYear] = CurrentFQ then true else false, type logical),
InsertCurrentFP = Table.AddColumn(InsertCurrentFQ, "IsCurrentFP", each if [FPeriodnYear] = CurrentFP then true else false, type logical),
InsertCurrentFW = Table.AddColumn(InsertCurrentFP, "IsCurrentFW", each if [FWeeknYear] = CurrentFW then true else false, type logical),
InsertPYTD = Table.AddColumn(InsertCurrentFW, "IsPYTD", each if CurrentYear-1 = [Year] and [Day of Year] <= CurrentDateRecord{0}[Day of Year] then true else false, type logical),
ListPrevFYDates = List.Buffer( Table.SelectRows( Table.ExpandTableColumn( Table.NestedJoin(
Table.AddIndexColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( List.Dates( PrevFiscalFirstDay, Number.From(CurrentFiscalFirstDay-PrevFiscalFirstDay),#duration(1,0,0,0)), Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "DateFY"}}), "Index", 1, 1), {"Index"},
Table.AddIndexColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( List.Dates( Date.AddYears( PrevFiscalFirstDay, -1), Number.From( PrevFiscalFirstDay - Date.AddYears( PrevFiscalFirstDay, -1)),#duration(1,0,0,0)), Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "DateFY"}}), "Index", 1, 1)
, {"Index"}, "Table", JoinKind.LeftOuter), "Table", {"DateFY"}, {"PrevDateFY"}), each [DateFY] <= CurrentDate)[PrevDateFY] ),
InsertPFYTD = Table.AddColumn(InsertPYTD, "IsPFYTD", each if [FiscalYearOffset] = -1 and List.Contains(ListPrevFYDates, [Date] ) then true else false, type logical),
RemoveToday = Table.RemoveColumns( if EndDate < CurrentDate then Table.SelectRows(InsertPFYTD, each ([Date] <> CurrentDate)) else InsertPFYTD, {"Day of Year", "FiscalFirstDay"}),
ChType = Table.TransformColumnTypes(RemoveToday,{{"Year", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfWeek", Int64.Type}, {"ISO Weeknumber", Int64.Type}, {"WeeknYear", Int64.Type}, {"MonthnYear", Int64.Type}, {"QuarternYear", Int64.Type}, {"Fiscal Period", Int64.Type}, {"WeekOffset", Int64.Type}, {"MonthOffset", Int64.Type}, {"QuarterOffset", Int64.Type}, {"YearOffset", Int64.Type}, {"FiscalYearOffset", Int64.Type}}),
ReorderColumns = Table.ReorderColumns(ChType, {"Date", "Year", "YearOffset", "YearCompleted", "QuarterOfYear", "Quarter & Year", "QuarternYear", "QuarterOffset", "QuarterCompleted", "MonthOfYear", "DayOfMonth", "Month Name", "MonthShortName", "Month Initial", "Month & Year", "MonthnYear", "MonthOffset", "MonthCompleted", "MonthEnding", "DateInt", "DayOfWeek", "DayOfWeekName", "Weekday Initial", "Day Type", "ISO Year", "ISO YearOffset", "ISO QuarterOfYear", "ISO Quarter", "ISO Quarter & Year", "ISO QuarternYear", "ISO QuarterOffset", "ISO Weeknumber", "Week & Year", "WeeknYear", "WeekOffset", "WeekCompleted", "WeekEnding", "Fiscal Year", "FiscalYearOffset", "Fiscal Quarter", "FQuarternYear", "IsCurrentFQ", "Fiscal Period", "FPeriodnYear", "IsCurrentFP", "Fiscal Week", "Fiscal Year & Week", "FWeeknYear", "IsCurrentFW", "IsAfterToday", "IsWorkingDay", "IsHoliday", "IsBusinessDay", "IsPYTD", "IsPFYTD"}, MissingField.UseNull)
in
ReorderColumns
//, documentation = [
//Documentation.Name = " fxCalendar",
//Documentation.Description = " Date table function to create an ISO-8601 calendar",
//Documentation.LongDescription = " Date table function to create an ISO-8601 calendar",
//Documentation.Category = " Table",
//Documentation.Version = " 1.30: Fixed DayOfWeekday added Day.Monday",
//Documentation.Source = " local",
//Documentation.Author = " Melissa de Korte",
//Documentation.Examples = { [Description = " See: https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390",
// Code = " Optional paramters: #(lf)
// (FYStartMonthNum) Month number the fiscal year starts, January if omitted #(lf)
// (Holidays) Select a query (and column) that contains a list of holiday dates #(lf)
// (WDStartNum) Switch default weekday numbering from 0-6 to 1-7 by entering a 1 #(lf)
// #(lf)
// Important to note: #(lf)
// [Fiscal Week] starts on a Monday and can contain less than 7 days in a First- and/or Last Week of a FY #(lf)
// [IsWorkingDay] does not take holiday dates into account #(lf)
// [IsBusinessDay] does take optional holiday dates into account #(lf)
// [IsPYTD] and [IsPFYTD] compare Previous [Day of Year] with the Current [Day of Year] number, so dates don't align in leap years",
// Result = " " ] }
// ]
// in
//Value.ReplaceType(fnDateTable, Value.ReplaceMetadata(Value.Type(fnDateTable), documentation))
//********************************* code end *********************************//
Here is how variables were used and can be back again to Parameters when comment out the line 1 and from 121 down in that case. The red frame must be disabled also (//).
DATA MODEL RELATIONSHIP
Finally…
How to connect the Date table to other tables?
· The best practice is following STAR SCHEMA
· Cardinality is Many to one (*:1) / One to many (1:*). Avoid Many to Many!
· Single cross filter direction.
Cross filter direction should flow from Date to Sales table and cannot go backwards (one way) as is shown on the picture.
More info of data model relationships here
You are safe from future problems in DAX related to inappropriate model relationships
Tags:
Power BIRecent Comments
Archives
Categories
Search
By transforming and visualizing our data, the dashboards that "Allure Analytics" created helped us in tracking and evaluating our employees' activity at all sales hierarchy levels – from the sales representatives to the national sales managers. Now we can easily see all of the key performance indicators of our employees, such as the number of calls made in a certain period, the number of customers visited, and the number of customers in the different categories. This helps us in keeping track of the achievements.
Julien B.
Associate Director, Pharmaceutical CompanyBy transforming and visualizing our data, the dashboards that "Allure Analytics" created helped us in tracking and evaluating our employees' activity at all sales hierarchy levels – from the sales representatives to the national sales managers. Now we can easily see all of the key performance indicators of our employees, such as the number of calls made in a certain period, the number of customers visited, and the number of customers in the different categories. This helps us in keeping track of the achievements.
Julien B.
Associate Director, Pharmaceutical CompanyBy transforming and visualizing our data, the dashboards that "Allure Analytics" created helped us in tracking and evaluating our employees' activity at all sales hierarchy levels – from the sales representatives to the national sales managers. Now we can easily see all of the key performance indicators of our employees, such as the number of calls made in a certain period, the number of customers visited, and the number of customers in the different categories. This helps us in keeping track of the achievements.
Julien B.
Associate Director, Pharmaceutical CompanyCategories
- Power BI (7)