Request: simple explanation of date/calendar in Power BI + auto time intelligence?

[Anonymous]

So Power BI has an option to automatically create a hidden time table, which apparently is not very good according to a lot of forum talk.

Instead most people recommend making your own calendar table. 

question: what is the advantage vs disadvantage of using the auto calendar table?

also what happens if you use your own calendar table + have the auto time intelligence option selected? is that just silly? 

Im really missing a basic calendar explanation video of the time intelligence. I don't care about all the advanced features of calendar, i just want the basics to work, which seems like a hassle. 

The Power BI team released a 1 hour video going through some time intelligence. Seriously, why is it so complicated that it needs 1 hour explanation just to work with a freaking calendar. 

https://www.youtube.com/watch?v=FxiAYGbCfAQ&t=3361s 

[waltheed]

I agree, it's really the best approach to create your own data table, and use that one for all relevant date fields in your data model. 

This is an short list of reasons why (from https://www.sqlbi.com/...) :

"... 

Why a reference Date table

The Auto Date/Time feature available in Power BI presents several limitations:

• It has a fixed set of rows.
• It does not handle fiscal years.
• It does not include weeks.
• It cannot be shared across different tables in the same data model.

Usually, it is necessary to disable the feature and to create a custom Date table. This task is repetitive and time consuming. Creating new Power BI models starting from a Power BI template containing a fully-featured Date table spares the user from writing the required DAX expression – as well as in setting the necessary properties to define display format, hierarchies, and visibility of the columns required in reports and calculations.

..." 

What problems do you have getting the basics to work? I may be able to give you some hints on that. 

[Anonymous]

I just need a calendar table with year, quarter, month, week, day.

Then i need to display sales records either by month or week.

Usually my sales records are coming in with two separate columns stating for example containing:
"year 2018", "month 1" and "Week 3"

Once i have that then i can make a graph that shows sales across either weeks, months or year.

[waltheed]

Here is a Power Query script that generates a simple date table. 

let
    Source = #date(2000, 1, 1),
    Custom = List.Dates(Source, Number.From(Date.AddDays(DateTime.LocalNow(), 1000))- Number.From(Source)  ,#duration(1,0,0,0)),
    #"Create Table" = Table.FromList(Custom, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Change Data Type" = Table.TransformColumnTypes(#"Create Table",{{"Column1", type date}}),
    #"Change Column Name" = Table.RenameColumns(#"Change Data Type",{{"Column1", "Date"}}),
    #"Create Year Column" = Table.AddColumn(#"Change Column Name", "Year", each Date.Year([Date]), type number),
    #"Create Month Column" = Table.AddColumn(#"Create Year Column", "Month", each Date.Month([Date]), type number),
    #"Create Day Column" = Table.AddColumn(#"Create Month Column", "Day", each Date.Day([Date]), type number),
    #"Create Quarter Column" = Table.AddColumn(#"Create Day Column", "Quarter", each Date.QuarterOfYear([Date]), type number),
    #"Create Week Column" = Table.AddColumn(#"Create Quarter Column", "Week", each Date.WeekOfYear([Date]), type number),
    #"Create DayOfYear Column" = Table.AddColumn(#"Create Week Column", "DayOfYear", each Date.DayOfYear([Date]), type number),
    #"Create DayOfWeek Column" = Table.AddColumn(#"Create DayOfYear Column", "DayOfWeek", each Date.DayOfWeek([Date],1) + 1, type number),
    #"Create Month Name Column" = Table.AddColumn(#"Create DayOfWeek Column", "Month Name", each Date.ToText([Date], "MMM")),
    #"Create Day Name Column" = Table.AddColumn(#"Create Month Name Column", "Day Name", each Date.ToText([Date], "ddd")),
    #"Create Year Month Column" = Table.AddColumn(#"Create Day Name Column", "Year Month", each Number.ToText([Year]) & "-" & Text.End("00" & Number.ToText([Month]), 2)),
    #"Capitalized Each Word" = Table.TransformColumns(#"Create Year Month Column",{{"Month Name", Text.Proper, type text}, {"Day Name", Text.Proper, type text}}),
    #"Create Year Week Column" = Table.AddColumn(#"Capitalized Each Word", "Year Week", each Number.ToText([Year]) & "-" & Text.End("00" & Number.ToText([Week]), 2)),
    #"Added Custom" = Table.AddColumn(#"Create Year Week Column", "Relative Year", each [Year] - Date.Year(DateTime.LocalNow())),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"DayOfWeek", "Day of Week"}, {"DayOfYear", "Day of Year"}})
in
    #"Renamed Columns"

In Power BI choose Get Data - Blank Query. 

Then click on the Advanced editor button. 

Paste this M-script in there. 

HTH