Contiguous date selection error - YTD LY

[Janx]

Hello all,

I have a problem with the YTD function. In a nutshell I have a table with Brand, Category, Franchise, Value, KPI etc.

I have created a YTD function

YTD = TOTALYTD(SUM('Per Day'[Value]),'Per Day'[MonthYear])

and a YTD LY function

YTD LY = CALCULATE([YTD],SAMEPERIODLASTYEAR('Per Day'[MonthYear]))

When I now create a report it works perfectly fine for Brand and Category. However, when I try to do it for Franchise and add YTD LY (YTD works fine) I get the error:

MdxScript(Model) (1, 50) Calculation error in measure 'Per Day'[YTD LY]: Function 'SAMEPERIODLASTYEAR' only works with contiguous date selections.

I tried to fix it with the help of other threads but it didn't seem to work (e.g. replacing 'SAMEPERIODLASTYEAR' with 'DATEADD' gives the same error). What am I doing wrong?

Thanks a lot in advance for your help!

[kcantor]

@Janx

Most often, issues with time intelligence come from the lack of a date dimension table. A seperate date table with all dates in the range is requried for that to work. If you are pulling the date from the fact table, you may not have information for weekends, holidays, etc. Check this link for more information.

http://www.powerpivotpro.com/2011/11/the-ultimate-date-table/

From your calculation, it looks like you are pulling the date from your fact table. Add a date table and give it another go.

[Janx]

@kcantor

Thanks a lot! Yes, that's correct. I'm pulling the date from my fact table. What I did now is to download this DateStream table and I've imported it to Power BI. Then I've linked the date column in my fact table with the date column in the date table (exact same format). Do I need to do something else? Because apparently, it's still not working.

[kcantor]

@Janx

You will need to change your DAX to reflect the new relationship. You have:

YTD LY = CALCULATE([YTD],SAMEPERIODLASTYEAR('Per Day'[MonthYear]))

It should now be something like:

YTD LY = CALCULATE([YTD],SAMEPERIODLASTYEAR('Date'[DateKey]))

I am also a fan of:

YTD LY = CALCULATE([YTD],DATEADD('Date'[DateKey], -1, year))

One example only. You will need to change both reflecting table and column names. Be sure to use the Date Key for the relationship and the DAX.

[Janx]

@kcantor

That's what I did, but unfortunately it still says the data is not contiguous. YTD is working, but YTD LY is not.

[v-qiuyu-msft]

Hi @Janx,

Based on my test, if the fact table date column is linked to the calendar table date column and the date column within the calendar table is continued, both YTD and YTD LY expressions can work. Please check attached .PBIX file. Compare it with your report to find where is the issue.

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[Janx]

@v-qiuyu-msft

Thanks a lot for your reply.

I also got to the point that I don't get the error anymore, HOWEVER - and this is also the case in your data - the YTD LY formula is not working correctly. For example, in your data set your 2016 data goes through June 2016. However, your YTD LY formula gives you the Revenues for whole 2015 instead of January - June 2015 only, which is the general idea of YTD comparison.

I have the very same problem in my data. I've uploaded my .PBIX file here. I've also included the calculation without using the date table ("YTD ERROR") for reference. As you see, the YTD and YTD LY does not lead to an error, however, it's not calculating last year's YTD correctly. I think there is some error in the date linking... Any ideas?

Thanks!

[kcantor · Accepted solution]

@Janx

In this case you will need to specify what you consider to be the calendar periods for a year. DAX is defaulting to Jan through Dec. Check out this blog for YTD custom dates.

http://www.kasperonbi.com/get-the-ytd-of-same-period-last-year-using-dax/

[Janx]

@kcantor

That makes sense, thanks a lot!

[KGrice]

Have you already checked this solution on PowerPivotPro? This error can happen because of the nature of totals rows. Not sure why you didn't see it before on your previous measures, but it's one more solution to try.