Dynamic Date Table In Power BI

Power BI / July 2, 2022 / , ,

Why do you need a date table?

In order to work with Time Intelligence Functions in Power BI, you must have a dedicated Date Table in your model.
Time Intelligence is the mechanism by which Power BI can calculate measures such as Month To Date, Year To Date etc.
 
A Date Table is one that meets the following requirements:
  • One field with the full date and a data type of Date.
  • The date field must not have any duplicates.
  • The date field must have no blank values.
  • The date field must be contiguous, i.e. there must be no gaps in between two dates in the range.
  • The date field must contain full years – at least 12 months, not necessarily a full calendar year from January to December.
  • The date table must be marked as a date table using the Power BI UI.
It is advised to have a consistent set of rules that define your date table in your organization.
  • If you have a dedicated data source such as a Data Warehouse, it is best practice to store it there for everyone to use.
  • However, if you wish to keep it contained in Power BI, you can generate a Power BI Template file with the code required to generate a date table from scratch. That method is what this article will focus on.

M-Code Example

The code below was added to a Blank query and will generate a full calendar from a specified start date, up until the end of the current calendar year. This means it will always forecast future dates with no manual intervention.
let
    //Fixed Start Date
    StartDate = #date(2021,1,1),
    //Dynamic End Of Current Year End Date
    EndDate = #date(Date.Year(DateTime.FixedLocalNow()), 12, 31),
    //Number of days used to determine length of list of dates to generate
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    //Create a list of dates between start and end date
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
    //Convert the list to a full table
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    //Give the Date field a proper name
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    //Proper data type for the date field to allow intelligent date aspects extraction
    #"Changed Type Of Date Field" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    //Get the Year of the date
    #"Inserted Year" = Table.AddColumn(#"Changed Type Of Date Field", "Year", each Date.Year([Date]), type number),
    //Get the Month of the date
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), type number),
    //Get the Mmonth Name of the date
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
    //Get the Quarter of the date
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), type number),
    //Get the Week Number of the year of the date
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([Date]), type number),
    //Get the Week Number Of The Month of the date
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date]), type number),
    //Get the Day Number of the month of the date
    #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([Date]), type number),
    //Get the Fay Number Of the Week of the date
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([Date]), type number),
    //Get the Day Number Of The year of the date
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), type number),
    //Get the Day Name of the date
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([Date]), type text),
    //Generate a finacial year value, using the #"Financial Year Offset" paramter number
    #"Insert Financial Year" = Table.AddColumn(#"Inserted Day Name", "Financial Year", each Date.Year(Date.AddMonths([Date],#"Financial Year Offset")), type number),
    //Generate a finacial month value, using the #"Financial Year Offset" paramter number
    #"Insert Financial Period" = Table.AddColumn(#"Insert Financial Year", "Financial Period", each Date.Month(Date.AddMonths([Date],#"Financial Year Offset")), type number),
    //Create an integer representation of the date for joining to data warehouse tables if needed
    DateID = Table.AddColumn(#"Insert Financial Period", "DateID", each Date.ToText([Date], "yyyyMMdd"), type number),
    #"Changed Type Of All Fields" = Table.TransformColumnTypes(DateID,{{"DateID", Int64.Type},{"Year", Int64.Type}, {"Day of Year", Int64.Type}, {"Day of Week", Int64.Type}, {"Day", Int64.Type}, {"Week of Month", Int64.Type}, {"Week of Year", Int64.Type}, {"Quarter", Int64.Type}, {"Month", Int64.Type}})
in
    #"Changed Type Of All Fields"
 
This example includes Financial Year and Financial Period fields as well. This is especially useful when building financial models where your source data is allocated to calendar dates and you need to calculate the financial period dynamically. 
 
This example calculates the financial period using a parameter to define the shift in months from the calendar date. The setup I used to tag August as the first period of the next financial year is as follows:
The data generated using this code looks as follows:

Tips

  • Remember to change the default aggregation of the numeric fields in the table to “Don’t Summarize”, otherwise you will have a bad time and the visualizations will try to sum the fields instead of using them as attributes from a dimension.
  • Use comments in your m-query code to add tooltips to the transformation steps for future developers to quickly get up to speed with the business logic of the code:

Variations

  • Start and End Date parameter options
    • Dynamic values using an existing table in the model. This instead of a fixed start date and and end date based on the current date:
      • StartDate = Record.Field(Table.Min(#”TableName”,”DateColumnName”),”DateColumnName”),
      • EndDate = Record.Field(Table.Max(#”TableName”,”DateColumnName”),”DateColumnName”),
  • Using CALENDAR or CALENDARAUTO DAX functions.
    • Both of these functions take some parameters and generate a single column table of dates.
      • Use CALENDAR when you want to provide the start and end date times yourself. This is similar to the method I used, however, it skips the list and table generation lines of code and compresses them into one. 
      • Use CALENDARAUTO when you want the table to automatically determine the min and max dates used in your whole model and auto generate the list from there. Note this options is significantly slower at reload time.

If you like what I do please consider supporting me on Ko-Fi

5 thoughts on “Dynamic Date Table In Power BI”

  2. Janett Gavia

    I have not checked in here for a while because I thought it was getting boring, but the last few posts are good quality so I guess I¦ll add you back to my daily bloglist. You deserve it my friend 🙂

  3. Chang Tarsis

    I just like the valuable info you provide to your articles. I’ll bookmark your blog and check again right here frequently. I’m somewhat sure I’ll be told many new stuff right right here! Good luck for the following!

  4. Tamil Whatsapp Status Video 2022

    Hello, i read your blog occasionally and i own a similar one and i was just curious if you get a lot of spam feedback? If so how do you protect against it, any plugin or anything you can advise? I get so much lately it’s driving me crazy so any assistance is very much appreciated.

Comments are closed.