Solved: Time Intelligence: TOTALMTD vs DATESMTD vs DATEADD - Page 2

cwayne758 · ‎11-13-2015

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 ways. 2 of my 3 give me the same answer.

Photo attached:

Thanks in advance 🙂

greggyb · ‎11-13-2015

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.

Check out the Power BI User Group of Philadelphia
Next meeting: October 5, 2016 Virtual meeting

View solution in original post

Time Intelligence: TOTALMTD vs DATESMTD vs DATEADD

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly