Forum Discussion

cwayne758's avatar
cwayne758
Icon for Helper IV rankHelper IV
10 years ago
Solved

Time Intelligence: TOTALMTD vs DATESMTD vs DATEADD

Hi,   I am trying to have a floating measure that tells us the total write-off amounts from the start of the current month to the current day in the current month.   I have done this 3 different ...
General Comment
Need Help
  • greggyb's avatar
    greggyb
    10 years ago

    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.

elliotdixon's avatar
elliotdixon
Icon for Responsive Resident rankResponsive Resident
10 years ago

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's avatar
greggyb
Icon for Resident Rockstar rankResident Rockstar
10 years ago

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's avatar
    elliotdixon
    Icon for Responsive Resident rankResponsive Resident
    10 years ago

    Cheers greggyb - its still trying to add a HolidayFlag column.

     

     

     

  • greggyb's avatar
    greggyb
    Icon for Resident Rockstar rankResident Rockstar
    10 years ago

    You'll have to change the table reference in the WorkdayFlag step to a table other than that defined in the HolidayFlag step.