Forum Discussion
Contiguous date selection error - YTD LY
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!
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/
9 Replies
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.
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.
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.
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.
