cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mvsvendsen
Advocate II
Advocate II

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

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

14 REPLIES 14
KathyPullin
Frequent Visitor

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! 

kcantor
Community Champion
Community Champion

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 Super User!




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

 

 

kcantor
Community Champion
Community Champion

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 Super User!




qa4432
Frequent Visitor

Thank you!!!!  This explanation helped me exponentially and engranged in my head about utilizing the Date Dimension correctly.

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.

 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

great !, it worked,  Many thanks

Yes, this works. But, WHY?

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.

Sumesh
Regular Visitor

Absolutely 100% correct

Hello Community, 

 

Could anyone get back to this post and try to help me? If I understood correctly, my relationship between Date table (as date type for sure) and transactional table is correct... How to fix this error or have some kind of workaround? Maybe I need to learn new functions to resolve. I am a junior in Power BI.

 

Problem persist...

When I select contiguous quarters in Donut chart object (eg: 1st, 2nd or 2nd 3th) it works fine, as desired, but as picture below when I select for example 1st and 4th problem appears. As you know Users always check this kind of thing and I do not want to show errors or any message they cannot understand (and they do not need to).

 

Images:

 

I think this was set correctlyI think this was set correctlyPlease HelpPlease Help

 

Thanks a lot in advance.

 

Jaderson Almeida
Business Coordinator

I forgot to share the Sample File

Jaderson Almeida
Business Coordinator

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.