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 ResidentTo use time intelligence you have to use a true date column. the time calculations will not work with a date key. I also find it useful with creating calculations in the context of a month to not only format as date but also use the transform tab to format my column to equal the end of the month.
Resident RockstarIf 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
HI All,
Realise this topic is marked as solved but I have the same issue. Need to calculate the total for last month.
greggyb love your link. Heaps of information.DATESMTD - I can't use this as my dates table does not continue past the date of the most recent data. (i.e. today)
DATEADD - when using a calculation
Last_Month_TotalKms = CALCULATE([Kms],DATEADD(Dates[Date],-1,month))
I just get the full total for all Kms. The DATEADD filter does not affect the calculation at all. Value does not change when anything in the DATEADD part of the formula is changed. (e.g. if I change it to -5 or the month to day nothing happens)
I am wondering if there is another way of approaching the question.
if there is any column calculation to show if in previous month.
e.g. I have a calcuation for if in previous week
IsInCurrentYear = if(YEAR(NOW())= [Year],1,0)
and this feeds
IsInCurrentWeek = if([isInCurrentYear] && WEEKNUM(NOW())=[WeekOfYearNumber],1,0)
And
IsInLastWeek = if([isInCurrentYear] && (WEEKNUM(NOW())-1)=[WeekOfYearNumber],1,0)
So if I could get another column for IsInPreviousMonth that would be great.
Anyone know how to do that?Cheers
- greggyb
First, is there anything preventing you from loading dates to the end of the current year in your date dimension?
Second, Power Query has a lot of built in date functions specifically to create flags like you have mentioned. I'd use those in Power Query rather than defining calculated columns in the data model if I were you. They specifically cover all of the use cases you've laid out, and cover year-end wrapping appropriately.
- elliotdixon
Hi greggyb
I am using the date table creation from
http://blogs.msdn.com/b/lukaszp/archive/2015/03/05/power-bi-date-filtering.aspx
//let // CreateDateTable = (StartDate, EndDate) => let StartDate=#date(2012,1,1), EndDate=#date(Date.Year(DateTime.LocalNow()),12,31), //Create lists of month and day names for use later on MonthList = {"January", "February", "March", "April", "May", "June" , "July", "August", "September", "October", "November", "December"}, DayList = {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"}, //Find the number of days between the end date and the start date NumberOfDates = Duration.Days(EndDate-StartDate), //Generate a continuous list of dates from the start date to the end date DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),Has been working great but I have only just noticed it seems to push the date out to the 30th of December instead of the 31st.
Strange.
the line
EndDate=#date(Date.Year(DateTime.LocalNow()),12,31),
Should do the work but for some reason isnt going right to the end.
