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:

  1. Name: StartDate. Type: Date, Current Value: 01-01-2018 or 01/01/2018
  2. 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.

 Finally…put it all together
 
Complete code with Parameters (Start & End dates)
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 (//).

Download v.2

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 BI

Leave a Comment