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:
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.
