Forum Discussion
Helper IVTime Intelligence: TOTALMTD vs DATESMTD vs DATEADD
If you want to understand time intelligence better in DAX, read this excellent blog post.
In short here is the behavior of the three functions you mentioned.
TOTALMTD(): This is only syntactic sugar, all it does is give you the following:
CALCULATE( [measure] ,DATESMTD(DimDate[Date]) )DATESMTD(): This works in a date dimension, which must have contiguous, nonrepeating dates from January 1 of the first year you have data to December 31 of the last year you have data. The function returns a 1 column table made up of dates between the first of the month of the current date in context and the current date in context.
DATEADD(): This essentially gives you a range of dates (one column table) based on the number of intervals you've requested in either direction. It does not behave intuitively compared to a DATEADD() function in any other language, and I am not aware of any circumstances we (we being a Microsoft BI consultancy with a number of DAX experts) have decided this is the right function to use for any of our work. There is a good description in the linked blog post at the top of my reply.
Responsive Resident
So I just took another look and worked out I could change the code
//Generate a continuous list of dates from the start date to the end date
DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 2, 0)),previously this was
//Generate a continuous list of dates from the start date to the end date
DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),Gather that 2 moves it one day forward.
Not sure how that works but it seems to do the job. Now back to the formula.
Resident RockstarThe #duration() constructor creates a Duration type, where the four numeric values correspond to days, hours, minutes, second. That List.Dates() that you're using increments by one day and two minutes at each step. List.Dates() can only return a date data type, though, so those minutes end up truncated from the output data values.
Here's what I use as my baseline date dimension:
let
Source = List.Dates(
#date(2010,1,1)
,Duration.Days( #date(2025,12,31) - #date(2010,1,1) ) + 1
,#duration(1,0,0,0) ),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
Year = Table.AddColumn(#"Changed Type", "Year", each Date.Year( [Date] )),
QuarterNumber = Table.AddColumn(Year, "QuarterNumber", each Date.QuarterOfYear( [Date] )),
FirstYear = List.First( QuarterNumber[Year] ),
QuarterIndex =
Table.AddColumn(QuarterNumber, "QuarterIndex", each let
Mult = [Year] - FirstYear
,Index = 4 * Mult + [QuarterNumber]
in
Index),
Quarter = Table.AddColumn(QuarterIndex, "Quarter", each Number.ToText( [Year] ) & " Q" & Number.ToText( [QuarterNumber] )),
MonthNumber = Table.AddColumn(Quarter, "MonthNumber", each Date.Month( [Date] )),
MonthIndex = Table.AddColumn(MonthNumber, "MonthIndex", each let
Mult = [Year] - FirstYear
,Index = 12 * Mult + [MonthNumber]
in
Index),
Month = Table.AddColumn(MonthIndex, "Month", each Date.ToText( [Date], "yyyy MMM" )),
MonthName = Table.AddColumn(Month, "MonthName", each Date.ToText( [Date], "MMMM" )),
MonthNameShort = Table.AddColumn(MonthName, "MonthNameShort", each Date.ToText( [Date], "MMM" )),
WeekNumber = Table.AddColumn(MonthNameShort, "WeekNumber", each Date.WeekOfYear( [Date] )),
WeekIndex = Table.AddColumn(WeekNumber, "WeekIndex", each let
Mult = [Year] - FirstYear
,Index = 53 * Mult + [WeekNumber]
in
Index),
Week = Table.AddColumn(WeekIndex, "Week", each Number.ToText( [Year] ) & " W" & Number.ToText( [WeekNumber], "00" )),
DayOfYear = Table.AddColumn(Week, "DayOfYear", each let
Leap = Date.IsLeapYear( [Date] )
,DOY = Date.DayOfYear( [Date] )
in
if not Leap
and [Date] >= #date([Year],3,1)
then DOY + 1
else DOY),
DayOfQuarter = Table.AddColumn(DayOfYear, "DayOfQuarter", each let
Leap = Date.IsLeapYear( [Date] )
,DOQ =
Duration.Days(
[Date] - Date.StartOfQuarter( [Date] ) )
+ 1
in
if not Leap
and [QuarterNumber] = 1
and [Date] >= #date([Year],3,1)
then DOQ + 1
else DOQ),
DayOfMonth = Table.AddColumn(DayOfQuarter, "DayOfMonth", each Date.Day( [Date] )),
DayOfWeek = Table.AddColumn(DayOfMonth, "DayOfWeek", each Date.DayOfWeek( [Date] )),
DayName = Table.AddColumn(DayOfWeek, "DayName", each Date.ToText( [Date], "dddd" )),
DayNameShort = Table.AddColumn(DayName, "DayNameShort", each Date.ToText( [Date], "ddd" )),
Weekday = Table.AddColumn(DayNameShort, "Weekday", each if [DayName] = "Saturday" or [DayName] = "Sunday"
then "Weekend"
else "Weekday"),
WeekdayFlag = Table.AddColumn(Weekday, "WeekdayFlag", each [Weekday] = "Weekday"),
MergeHolidays = Table.NestedJoin(WeekdayFlag,{"Date"},Holidays,{"Date"},"NewColumn",JoinKind.LeftOuter),
ExpandHolidays = Table.ExpandTableColumn(MergeHolidays, "NewColumn", {"HolidayName"}, {"HolidayName"}),
HolidayFlag = Table.AddColumn(ExpandHolidays, "HolidayFlag", each [HolidayName] <> null),
WorkdayFlag = Table.AddColumn(HolidayFlag, "WorkdayFlag", each [WeekdayFlag] and not [HolidayFlag]),
CurrentYear = Table.AddColumn(WorkdayFlag, "CurrentYear", each Date.IsInCurrentYear( [Date] )),
CurrentQuarter = Table.AddColumn(CurrentYear, "CurrentQuarter", each Date.IsInCurrentQuarter( [Date] )),
CurrentMonth = Table.AddColumn(CurrentQuarter, "CurrentMonth", each Date.IsInCurrentMonth( [Date] )),
CurrentWeek = Table.AddColumn(CurrentMonth, "CurrentWeek", each Date.IsInCurrentWeek( [Date] )),
Today = Table.AddColumn(CurrentWeek, "Today", each [Date] = DateTime.Date( DateTime.LocalNow() )),
CurrentYTD = Table.AddColumn(Today, "CurrentYTD", each Date.IsInYearToDate( [Date] )),
CurrentQTD = Table.AddColumn(CurrentYTD, "CurrentQTD", each [CurrentQuarter]
and [Date] <= DateTime.Date( DateTime.LocalNow() )),
CurrentMTD = Table.AddColumn(CurrentQTD, "CurrentMTD", each [CurrentMonth]
and [Date] <= DateTime.Date( DateTime.LocalNow() )),
CurrentWTD = Table.AddColumn(CurrentMTD, "CurrentWTD", each [CurrentWeek]
and [Date] <= DateTime.Date( DateTime.LocalNow() )),
#"Changed Type1" = Table.TransformColumnTypes(CurrentWTD,{{"Year", Int64.Type}, {"QuarterNumber", Int64.Type}, {"DayOfYear", Int64.Type}, {"DayOfQuarter", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DayOfWeek", Int64.Type}, {"WeekNumber", Int64.Type}, {"MonthNumber", Int64.Type}, {"QuarterIndex", Int64.Type}, {"MonthIndex", Int64.Type}, {"CurrentYear", type logical}, {"CurrentYTD", type logical}, {"WeekIndex", Int64.Type}, {"Quarter", type text}, {"Month", type text}, {"Week", type text}, {"DayName", type text}, {"Weekday", type text}, {"WeekdayFlag", type logical}, {"CurrentQuarter", type logical}, {"CurrentMonth", type logical}, {"CurrentWeek", type logical}, {"Today", type logical}, {"CurrentQTD", type logical}, {"CurrentMTD", type logical}, {"CurrentWTD", type logical}, {"DayNameShort", type text}, {"MonthName", type text}, {"MonthNameShort", type text}, {"HolidayFlag", type logical}, {"WorkdayFlag", type logical}})
in
#"Changed Type1"
Edit: forgot to address the problem in your IsInPreviousMonth code:
IsInPreviousMonth = Table.AddColumn(IsInPreviousMonth, "IsInPreviousMonth", each Date.IsInPreviousMonth([Date]))
The table which you are adding this column to must be a prior defined binding in the let block. You are referencing the table defined by the current binding. It should beL
IsInPreviousMonth = Table.AddColumn(#"The previous step, not IsInPreviousMonth", "IsInPreviousMonth", each Date.IsInPreviousMonth([Date]))
- elliotdixon
Hi greggyb, I tried loading your date dimension in but get an error around holidays.
Any ideas?
Would be good to try and load up yours and give it a shot. I never thought of having leap year workarounds included. Always just accepted a little gap in my graphs. :smileyvery-happy:
- greggyb
elliotdixon, apologies. I have a merge in there to a holiday table that would be expected to be maintained by hand by client resources - that could live in a flat file or be hand-entered in Power BI, or live in a relational source.
The following snippet should be altered:
// Power Query // Snippet from date dimension above .... WeekdayFlag = Table.AddColumn(Weekday, "WeekdayFlag", each [Weekday] = "Weekday"), MergeHolidays = Table.NestedJoin(WeekdayFlag,{"Date"},Holidays,{"Date"},"NewColumn",JoinKind.LeftOuter), ExpandHolidays = Table.ExpandTableColumn(MergeHolidays, "NewColumn", {"HolidayName"}, {"HolidayName"}), HolidayFlag = Table.AddColumn(ExpandHolidays, "HolidayFlag", each [HolidayName] <> null), WorkdayFlag = Table.AddColumn(HolidayFlag, "WorkdayFlag", each [WeekdayFlag] and not [HolidayFlag]), .... // Comment and alter as follows: ... WeekdayFlag = Table.AddColumn(Weekday, "WeekdayFlag", each [Weekday] = "Weekday"), // MergeHolidays = Table.NestedJoin(WeekdayFlag,{"Date"},Holidays,{"Date"},"NewColumn",JoinKind.LeftOuter), // ExpandHolidays = Table.ExpandTableColumn(MergeHolidays, "NewColumn", {"HolidayName"}, {"HolidayName"}), // HolidayFlag = Table.AddColumn(ExpandHolidays, "HolidayFlag", each [HolidayName] <> null), WorkdayFlag = Table.AddColumn(HolidayFlag, "WorkdayFlag", each [WeekdayFlag] ), // and not [HolidayFlag]), .... - elliotdixon
- greggyb
You'll have to change the table reference in the WorkdayFlag step to a table other than that defined in the HolidayFlag step.