If you are just getting started with Power BI you may be seeing a lot of references to a 'Calendar' Table or 'Date Table. Maybe you already have a handle on this aspect of PBI, but a lot of people seem not to understand how to get started. If you forego having a 'Calendar' Table altogether, you can end up having a very cumbersome file and you won't be able to take advantage of many of the awesome date/time functions offered through Power BI. There are quite a few good resources already out there. This post points you to a few of those and helps you get started in both M and DAX.
To turn off Time intelligence - here is a great blog resource:
sqlbi - DAX 101: Automatic time intelligence in Power BI
You want your date table to encompass all of the dates you will be using (so if you have a lost of historic data, you will need your calendar to cover the earliest dates you are utilizing). Guy in a Cube has a great video to get started with a DAX calendar. Here is some DAX based on that:
Dates =
VAR BaseCalendar =
CALENDARAUTO (6)
RETURN
GENERATE (
BaseCalendar,
VAR BaseDate = [Date]
VAR YearDate = YEAR ( BaseDate)
VAR MonthNumber = MONTH ( BaseDate)
RETURN ROW (
"Day", BaseDate,
"Year", YearDate,
"Month Number", MonthNumber,
"Month", FORMAT ( BaseDate, "mmmm"),
"Year Month", FORMAT (BaseDate, "mmm yy"),
"Quarter", "Q"&FORMAT (BaseDate, "q")
)
)
If you would like something a little more manual and spelled out, this is a good resource:
ArcherPoint - Creating A Date Table In Power BI
If you want to get started with a Calendar Table in Power Query (M), here are two samples you can test out:
1. This one looks at the dates in a Sales table that has opportunities listed out in the future:
let
StartDate = List.Min(Table.Column(Sales,"ContractDate")),
EndDate = List.Max(Table.Column(Sales,"ContractDate")),
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1","Date"}}),
AddYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), type number),
AddQuarter = Table.AddColumn(AddYear, "Quarter", each Date.QuarterOfYear([Date]), type number),
AddCY = Table.AddColumn(AddQuarter, "CY", each if Date.IsInCurrentYear([Date]) then 1 else 0),
AddCQ = Table.AddColumn(AddCY, "CQ", each if Date.IsInCurrentQuarter([Date]) then 1 else 0),
ThreeYearsPrior = Table.AddColumn(AddCQ,"3 Year Lookback", each if Date.IsInPreviousNYears([Date],3) then 1 else 0),
AddPY = Table.AddColumn(ThreeYearsPrior,"PY", each if Date.IsInPreviousYear([Date]) then 1 else 0),
AddMonthNumber = Table.AddColumn(AddPY,"Month", each Date.Month([Date]), type number),
AddMonthName = Table.AddColumn(AddMonthNumber,"Month Name", each Date.MonthName([Date]), type text),
#"Changed Type" = Table.TransformColumnTypes(AddMonthName,{{"PY", Int64.Type}, {"3 Year Lookback", Int64.Type}, {"CQ", Int64.Type}, {"CY", Int64.Type}})
in
#"Changed Type"
2. This one is based on data that has only populated out to the current day and we want the Calendar table to go to the end of the current calendar year:
let
StartDate = List.Min(Table.Column(Finance,"Month")),
EndDate = Date.EndOfYear(List.Max(Table.Column(Finance,"Month"))),
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1","Date"}}),
AddYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), type number),
AddQuarter = Table.AddColumn(AddYear, "Quarter", each Date.QuarterOfYear([Date]), type number),
AddMonth = Table.AddColumn(AddQuarter, "Month", each Date.Month([Date]), type number),
AddMonthName = Table.AddColumn(AddMonth,"Month Name", each Date.MonthName([Date]), type text),
AddCY = Table.AddColumn(AddMonthName, "CY", each if Date.IsInCurrentYear([Date]) then 1 else 0),
AddCQ = Table.AddColumn(AddCY, "CQ", each if Date.IsInCurrentQuarter([Date]) then 1 else 0)
in
AddCQ
Here are some resources I used to eventually build my tables:
https://community.powerbi.com/t5/Desktop/Date-Dimension-Table-that-Dynamically-Pulls-Start-and-End-dates/td-p/76870
INSIGHTS & OUTLIERS - Need a Good Date Table for Power BI?
POWER BI TIPS AND TRICKS - Building Date Table from Scratch
If you have some awesome tip, please share them in the comments.