DAX: YTD and MTD questions

[JBeyers]

Hi all,

I have two questions regarding the calculation of YearToDate and MonthToDate:

1) I do not have unique timestamps in my dataset. I'm not able to use DAX functions like TOTALYTD() because my timestamps are not unique. How do I solve this?

2) I want to compare YTD values of the current year with the average YTD values of previous years. How do I calculate the average YTD values of previous years?

Thanks in advance!

[WillT]

You could also use the CALENDARAUTO() function to create a new Calculated Table with a unique column of dates from elsewhere in your model. HTH!

[kcantor]

You could try creating a DIMDATE table to relate to your data. This table could be used for unique time stamps. I use one and link it to my performance table. It allows me to use YTD and MTD.

[joeort]

How did you create your DIMDATE table? Does anything change in PowerBI Desktop vs PowerPivot (which I was easily running time intelligence calcs).?

[Seth_C_Bauer · Accepted solution]

@joeort Depending on what you have access to, you can create a date dimension table in SQL, or Excel.. There are a bunch of different posts on how to create these. Below are just a few.

SQL:

http://www.bidn.com/blogs/MikeDavis/ssis/1519/create-date-dimension-with-fiscal-and-time

or

http://sqldusty.com/2012/04/12/create-date-dimension-script/

Excel:

http://msbiacademy.com/?p=4731

or

http://kohera.be/en/blog/detail/how-to-create-a-date-table-in-powerpivot-with-one-single-value-2

[chrislbs]

I had similar difficulty on a first pass through YTD functions and then all was revealed when I created a Date table and then joined that back to my parent table's date field. 

YTD Sales = TOTALYTD(sum('Parent Sales Report'[Sales]),'Parent Sales Report'[Date])

     does not yield what I expected (ie. Feb is not Feb + Jan) 

YTD Sales = TOTALYTD(sum('Parent Sales Report'[Sales]),SeparateDateTable[Date])

magically does (Feb = Feb + Jan) 

@Baskar has a nice post here to make the date table using the Advanced Editor. 

https://community.powerbi.com/t5/Desktop/How-do-i-create-a-date-table/td-p/23896

[nchambe]

Here's my preferred date table based on another solution in the same suggested post. I've made some modifications and additions. The formulas are also copy-and-paste ready so you won't run into any curvy quote errors. https://sharepointlibrarian.com/2018/02/12/how-to-create-a-powerful-date-table-or-datekey-in-power-b...

[Greg_Deckler]

Sample data would help tremendously, otherwise, we really have no idea what you are dealing with or how to specifically help you beyond mere generalities.

So, here are some generalities. http://www.daxpatterns.com. Check out the Time Intelligence patterns that do not rely on DAX Time Intelligence functions.

You could create a unique date/time table. From your data, you could create a second query, only pull in the date/time column and specify "unique values only". Boom, unique date/time table. Then you just relate your data table to this date/time table. There are also free date/time tables in the Azure Data Marketplace. DateStream and Date Dimension are two examples.

Finally, you could do something like this =AVERAGEX(FILTER('MyTable',[YEAR]=2014),[Value])

You could also just do an AVERAGE([Value]) and then put that in a matrix with YEAR as a row and this new measure as a column and then it will automagically be filtered by the rows in the matrix.

Again, impossible to really get specific without sample data or something to go on.