UPDATE 2020-11-10: You can find more complete detailed and optimized examples for this calculation in the DAX Patterns: Standard time-related calculations article+video on daxpatterns.com.. How Are We Doing THIS Year Versus the Same Time LAST Year? When you create a year-over-year in DAX, you usually compare two set of dates from the calendar, regardless of the presence of data in all the days of the period. For instance, getting a“Year to Date” calculation for, say, Total Sales, is pretty straightforward: [YTD Sales] = CALCULATE([Total Sales], DATESYTD(Calendar[Date])) Back to the… This is a pretty common question, and a pretty common need. We don’t use the date table as it would give us 12/31/2019. Creating date-based functions in DAX in SSAS Tabular; Cumulative totals (TotalYTD, DatesYTD, etc) The same period in a previous month, quarter or year (this blog) Using PARALLELPERIOD to get the whole of the last period for 1st of Sep 2006, it will return date period of 1st of Sep 2005. Attachments. DAXSampleData.xlsx. We want to get the range of datum[Date] values from the first day of the previous year: FIRSTDATE(DATEADD(datum[Date],-12,MONTH)) Hi there, I have a question about what the below Dax function is actually doing with the date range. DAX Last Year to Date. First, there is a function SAMEPERIODLASTYEAR which should monitor business results in the same period last year. But there’s no DAX function that just DOES this. Here we use the LASTDATE on the Date column in the Sales table to determine last date of the current selected year in the matrix. On that Date table you can add in months, years, etc to have a nice slicer. A possible solution is to create a calculated column in the Date table in order to remove dates in the past that should be ignored. We can use the DAX function DATESBETWEEN to create a custom date range. Time Intelligence functions are often used for reviewing implementation of the goals at the same time last year. So only dates from the first of last year to today's date last year will be included. In our case we want the sum of sales from the same period last year, that means from 1/1/2009 to 6/1/2009. This blog shows how to use DAX date functions to summarise data, assuming that you have already created a calendar table. In what follows we will mention a few of DAX functions that cover this area. Lets look at quarter level, just remove the month and add quarter date field into table visual as … I marked your date table as "Date Table" which is good to do when using the time intelligence functions in DAX. If filter context is in DAY level; it will return the same DAY last year, if the filter context is in Month level, it will return same Month last year. Ask Question Asked 1 year, 8 months ago. SamePeriodLastYear returns the equivalent period to the filter context from last year. The below DAX Function is the function for same period last year, but if the date is the 21st of this month, is the below function measuring from the 1st to the 21st or the 1st to the 31st (Full month). 0. This gives us “8/8/2019” for the last sales date and then move it back one year to “8/8/2018”. ... How to show DAX Cumulative Sum of Current Year and Previous Year on same visual? For Q4 of 2006 it will return Q4 of 2005. I want to extract Units Sold as of same day (Last Year) based on the Order Date Want to compare Units Sold Today and Last Year same Day using DAX formula. For example, if the fiscal year 2008 starts on July 1, 2007, then the Date table must include all the days from July 1, 2007 to June 30, 2008. Trying to avoid filters on the report Example 12/17/2018 and 12/17/2017 Thanks for your help . Similarly, if you see the data at Quarter level, it would be same period but last year. If the report only references fiscal years, then the date table must include all the dates from the first to the last day of a fiscal year. If the last date in the fact table is September 5th for the current year, then all the dates after September 5th in previous years can be marked to avoid considering them in the calculation. Well we could make the formula ourself. As you can see, this time DAX change it’s behaviour and returns the TOTAL Sales for same month but last year.