What is the best way for creating a calendar table in the DateTime format in Power BI?

[HamidBee]

Hi All. What is the best way for creating a calendar table in the DateTime format in Power BI?. I am working with two datetime colums. one is start time and one is for end time. The idea is to create a calendar table and join it to these two columns and use the role playing dimensions in power bi. I'm not sure what the best way to create the table is.

[HamidBee · Accepted solution]

Hi All,

I found a brilliant solution from:

https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

to creating a comprehensive calendar table in the DateTime format in Power BI using Power Query. The script provided below allows for a quick setup and includes a wide range of columns, catering to both standard calendar years and financial years.

How to use the Script?

1. Create a new blank query in Power BI.
2. In the Power Query Editor window, go to the View tab, and click on Advanced Editor.
3. Copy and paste the entire script below:

let
    StartDate = #date(StartYear,1,1),
    EndDate = #date(EndYear,12,31),
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "FullDateAlternateKey"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"FullDateAlternateKey", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([FullDateAlternateKey]), type number),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([FullDateAlternateKey]), type number),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([FullDateAlternateKey]), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([FullDateAlternateKey]), type number),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([FullDateAlternateKey]), type number),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([FullDateAlternateKey]), type number),
    #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([FullDateAlternateKey]), type number),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([FullDateAlternateKey]), type number),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([FullDateAlternateKey]), type number),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([FullDateAlternateKey]), type text)
in
    #"Inserted Day Name"​

Configuration You'll need to configure the Date table based on your needs. The first few lines of the script are where you can set your configurations.

Best Practice Suggestion Consider creating a dataflow entity for the Date table since it's something you'll likely need across multiple Power BI files. This will streamline your work and ensure consistency across your projects.

Considerations

• This script does not include public holidays. If you need them, you'll have to incorporate another solution.
• It's not set up for fiscal weeks. If your scenario requires this, modifications to the script will be necessary.
• For use in multiple Power BI files, a dataflow is recommended to avoid redundancy.

For a deeper understanding and additional customization, such as adding public holidays or learning more about date dimensions, you can refer to Reza Rad's articles and resources. He's a wealth of knowledge with extensive experience in Microsoft technologies, particularly BI.

Hope this helps anyone looking to efficiently create a date dimension in Power BI!

[parry2k]

@HamidBee 

I hope this helps, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist