cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mvsvendsen Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
mvsvendsen Frequent Visitor
Frequent Visitor

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.

14 REPLIES 14
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




mvsvendsen Frequent Visitor
Frequent Visitor

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

 

 

Super User
Super User

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.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




mvsvendsen Frequent Visitor
Frequent Visitor

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.

mvsvendsen Frequent Visitor
Frequent Visitor

Re: Lab 2 SAMEPERIODLASTYEAR Only works with contiguous date selections

 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

mvsvendsen Frequent Visitor
Frequent Visitor

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.

KathyPullin Frequent Visitor
Frequent Visitor

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! 

Highlighted
harsal Frequent Visitor
Frequent Visitor

Re: Lab 2 SAMEPERIODLASTYEAR Only works with contiguous date selections

Yes, this works. But, WHY?

harsal Frequent Visitor
Frequent Visitor

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.