Reply
Frequent Visitor
Posts: 7
Registered: ‎01-19-2016
Accepted Solution

Lab 2 SAMEPERIODLASTYEAR Only works with contiguous date selections

Hi all,

 

I've tried to add a slicer on ProductCategory on the graph with Total Sales and LY Sales and i get an error message when i filter to one category.

 

The message i get is MdxScrpt(Model) (1, 58) Calculation error in measure 'Sales'[LY Sales]: Function 'SAMEPERIODLASTYEAR' only works with contiguous date selections.

 

Anyone have a clue why this happens?

 

snip1.PNGsnip2.PNG

snip3.PNG


Accepted Solutions
Frequent Visitor
Posts: 7
Registered: ‎01-19-2016

Re: Lab 2 SAMEPERIODLASTYEAR Only works with contiguous date selections

Got this answer from MS

 

If it is a measure which is using any date function which expects contiguous date range, bi-directional filter ends up removing some dates and thus its no longer a contiguous date range which could be crashing the measure.

View solution in original post


All Replies
New Contributor
Posts: 652
Registered: ‎08-27-2015

Re: Lab 2 SAMEPERIODLASTYEAR Only works with contiguous date selections

That error generally means that you are missing a date in your calendar or trying to filter beyond the means of your calendar. Usually I see it when trying to force a year over year view on a normal calendar using weeks but, since you are not doing that, I would check my calendar for missing dates. If you pulled the calendar in through query, check to make sure you didn't filter out any necessary dates.

Frequent Visitor
Posts: 7
Registered: ‎01-19-2016

Re: Lab 2 SAMEPERIODLASTYEAR Only works with contiguous date selections

i'm using the sample dataset from the lab 2 pbix, the date dimension have all dates from 2006.06.01 to 2015.01.31

 

it seems like by filtering to a product i filter out some of the dates on the date dimension. ?

 

am i missing something about the "cross filter direction" or do i need to use relatedtable() in the measure to make it work? seems strange.

 

i've recreated the issue in a small sample dataset in this pbix file

 

https://www.dropbox.com/s/bhdapywx4y9xbat/SamePeriodLastYear_Test.pbix?dl=0

 

data in this excel sheet

 

https://www.dropbox.com/s/gtxjscauslfvp4f/Book1.xlsx?dl=0

 

 

Highlighted
New Contributor
Posts: 652
Registered: ‎08-27-2015

Re: Lab 2 SAMEPERIODLASTYEAR Only works with contiguous date selections

Okay, I found your problem. Let me explain it this way:

Filters flow down hill from your look up tables to the fact table. You never populate slicers from the fact table. Your current date slicersis pulled from the fact table and not your date table.  Click on your date slicer then change it to reflect the date field from your date table.  You may have something else going on but that will help with your date slicer.

Frequent Visitor
Posts: 7
Registered: ‎01-19-2016

Re: Lab 2 SAMEPERIODLASTYEAR Only works with contiguous date selections

Thanks,

 

The date slicer was just to show the values of the fact table. the top graph is with the date dim and the two measures. if you try to filter that by the product slicer below the problem occurs.

Frequent Visitor
Posts: 7
Registered: ‎01-19-2016

Re: Lab 2 SAMEPERIODLASTYEAR Only works with contiguous date selections

[ Edited ]

 Okay a coleague pointed out that it works if i make the "CrossFilterDirection" between the datedim and the sales as "Single" it works.

 

Not sure i understand why "Both" Breakes the visulization

Frequent Visitor
Posts: 7
Registered: ‎01-19-2016

Re: Lab 2 SAMEPERIODLASTYEAR Only works with contiguous date selections

Got this answer from MS

 

If it is a measure which is using any date function which expects contiguous date range, bi-directional filter ends up removing some dates and thus its no longer a contiguous date range which could be crashing the measure.

Frequent Visitor
Posts: 5
Registered: ‎08-30-2016

Re: Lab 2 SAMEPERIODLASTYEAR Only works with contiguous date selections

I know this was posted a while ago, but I still want to thank you for posting this solution.   So thank you!  You've saved me countless hours! 

Frequent Visitor
Posts: 2
Registered: ‎01-05-2016

Re: Lab 2 SAMEPERIODLASTYEAR Only works with contiguous date selections

Yes, this works. But, WHY?

Frequent Visitor
Posts: 2
Registered: ‎01-05-2016

Re: Lab 2 SAMEPERIODLASTYEAR Only works with contiguous date selections

OK, now I know why it works. Both Cross Filter Direction for date is not yet supported. Therefore they have designed an error, which is the one we see here. Once you change the Cross Filter Direction to Single, there is nothing that isn't supported anymore, therefore the error goes away.