Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
gvg
Post Prodigy
Post Prodigy

What is contiguous selection?

Some functions only work with "contiguous selections". What are they exactly? For example, 

 

Function 'SAMEPERIODLASTYEAR' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion. And what is meant here "date column is not unique"?

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Right, but it's not a big deal 🙂   You need to create a separate date table, then relate your current table, to the date table.

 

Probably the easiest way is to use New Table in the Modeling ribbon, and type "Dates = CALENDARAUTO()"

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

All of the time intelligence functions require a date table (more like a date column) as a parameter.  That date column has to include every date (say, incluing weekends and holidays) with no "gaps".  That is what is meant by continguous (which, sounds similiar to "continuous").

 

Some weird things can happen though, if you are trying to use these time intelligence functions like SAMEPERIODLASTYEAR... if you use a slicer to remove rows from the middle.   Same period last year wants to look at just exactly 1 "period" -- that can be a week, day, month, 4.6 weeks... but they need to be 1 continuous range not like "the 1st and 4th week of July" (which would have a gap... in the 2nd and 3rd weeks, and is not allowed).

 

 

 

Does that mean that I cannot use time intelligence functions for a data set like this:

 

Date          Sales
2016-01-01    100
2016-01-04     80
2016-01-20    200
2017-01-02     60
2017-01-05     40
2017-01-21     20

 

I.e. I will not be able to compare January-2016 to January-2017 because dates are not "contiguous"?

Anonymous
Not applicable

Right, but it's not a big deal 🙂   You need to create a separate date table, then relate your current table, to the date table.

 

Probably the easiest way is to use New Table in the Modeling ribbon, and type "Dates = CALENDARAUTO()"

 

We are facing this issue in those cases when sale of a particular brand was not in previous year but it is in current year. How can we resolve this issue if getting the same error? (Function 'SAMEPERIODLASTYEAR' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion.). Is there any way to define last year value as zero for all those cases where no value exists? I have tried with IFERROR and HASONEVALUE but these are also not working.

 

Hi! Could you please help me? I created a new calendar table for reference, activated time intelligence, built a relationship between my two tables (calendar table and Sales data dump table), but can't figure out my formula now.

TotalSales LAST YEAR = Calculate([Total Sales],DATEADD('Sales Data Dump'[Posting Date],-1,YEAR))
I tried to use my date reference table instead - TotalSales LAST YEAR = Calculate([Total Sales],DATEADD('Calendar Table'[Date],-1,YEAR)) - but it's not working either.
 
My Total Sales are calculated this way: 
Total Sales = SUM('Sales Data Dump'[Sales Amount (Actual)])
 
I'm missing something...

OK, now I understand, why they talk in many threads about having a separate date table! Thanks!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.