Modelling: how to correctly use time intelligence and Calculate?

[JulieGueho]

Hi,

I'm doing classic sales report and I'm struggling to use Time Intelligence functions such as SAMEPERIODLASTYEAR, PREVIOUSMONTH, etc...

- I have a Dates table.

- I have a Sales table with sales by day for the two past years.

- The table are related by the datekey column

Problem 1: Year over year

I created two measures

Sales This Year = TOTALYTD(SUM(Revenue), Dates[Date])

Sales Last Year = CALCULATE([Sales This Year] , SAMEPERIODLASTYEAR(Dates[Date]))

-> I get the right values but if I try to plot the two measures by month on a bar chart, I get the non contiguous date error. I guess that's because some month don't have any sales last year.

Problem 2: Month over month

I created two measures

Sales July 2015 = CALCULATE(SUM(Revenue), DATESBETWEEN(Dates[Date], DATE(2015,7,1), DATE(2015,7,31)))

Sales Previous Month = CALCULATE([Sales July 2015], PREVIOUSMONTH(Dates[Date]))

-> I get Sales July 2015 = Sales Previous Month

If I do: Sales Previous Month = CALCULATE([Sales July 2015], PREVIOUSMONTH(DATESBETWEEN(Dates[Date], DATE(2015,7,1), DATE(2015,7,31))))

it works fine.

I don't get how the time intelligence functions are working. What am I missing??

[kcantor]

Honestly, I believe it is easier to use the DATEADD in this instance:
Sales Last Year = CALCULATE([Sales This Year] , DATEADD(Dates[Date]),-1,YEAR)

[JulieGueho · Accepted solution]

Thanks for your answer. I have the same error though.

MdxScript(Model) (1, 71) Calculation error in measure 'Sales'[Sales Last Year]: Function 'DATEADD' only works with contiguous date selections.

I think it doesn't work because I'm missing values for my previous year.

The workaround I found was to use the filter YEAR(Today()) = Dates[Year] and YEAR(Today()) -1 = Dates[Year]

[nmorneau]

Hello,

Can you explain what you mean by this?

The workaround I found was to use the filter YEAR(Today()) = Dates[Year] and YEAR(Today()) -1 = Dates[Year]

I am having the same issue where I have some days that are missing transactions and getting a the same error with every time intelligence function I use.

Thanks,

Nick