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

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
Anonymous
Not applicable

Contiguous date problem with Country filter

Hello,

 

In my current project, I have following three tables:

1) date dimension table where I have dates for everyday since 2019

2) country master table

3) website data table (which has a many-to-one relationship both with the date dimension and country master table)

 

I have put two filters: one filter to select date (from the date dimension table) and a second filter to select country (from the country master table). In-order to calculate YTD, I am using the function SAMEPERIODLASTYEAR. Everything works fine when the country filter is deselected. As soon as I select the particular country then it shows the following error:

 

chauhanpooja_0-1630267237184.png

 

I tried to follow the following link (also tried several things i.e.

IF(HASONEVALUE(Dates[Financial Year]),FIRSTDATE(DATEADD(Dates[Date], -1, YEAR)),BLANK())

) but didn't get any solution so far. Any help would be great! Thank you!

 

Ref: https://p3adaptive.com/2014/01/defanging-the-contiguous-date-selections-error/

7 REPLIES 7
Greg_Deckler
Community Champion
Community Champion

@Anonymous Do you have a date table with contiguous dates? Personally, I don't recommend using TI functions and just using non-sugar syntax functions instead. For example, if you want the first date a year ago, you can simply use. DATE([Financial Year]-1,1,1) for example. 

 

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Dear @Greg_Deckler ,

 

First of all, thank you so much for the help!

I tried your solution I created a column with DATE([Financial Year]-1,1,1) function and its returns 01/01/2020. but I am using the financial year which should be 01/04/2020. and also i can not select DATE([Financial Year]) column in measure so I creat a column. so how can we gain financial year do you have any idea?

@Anonymous Ah, well if you are using a financial year then I would definitely avoid TI functions as they are designed specifically to work with a standard Gregorian calendar, which essentially makes them next to useless. So, the answer to your question lies in how good of a financial calendar you have. Can you provide a sample of your financial calendar? Do you have a column in your financial calendar that is the day of the year for the financial calendar? For example, 1/4/2020 would have a value of 1, 1/5/2020 would be 2 and so on. If you have that then the answer is essentially a LOOKUPVALUE or MAXX(FILTER()) situation such as:

MAXX(FILTER('Dates',[Year] = [Financial Year] - 1 && [Day of Year] = 1),[Date])


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Dear @Greg_Deckler ,

 

Basically i am calulating percentage diffrence with respect to last year.

 

for that i have created this measure : 

 

%ChangeSessions =
var cp = SUM('Website Performance'[Sessions])
var pp = CALCULATE(SUM('Website Performance'[Sessions]), DATESBETWEEN(Dates[Date], [StarOfLastYear], [EndOfLastYear]))
Return
DIVIDE((cp - pp), pp, 0)
 
StarOfLastYear = FIRSTDATE(SAMEPERIODLASTYEAR(Dates[Date]))
EndOfLastYear = [StarOfLastYear] + [DaysInThisPeriod] - 1
DaysInThisPeriod = DATEDIFF([StartOfThisPeriod], [EndOfThisPeriod], DAY) + 1
StartOfThisPeriod = FIRSTDATE(Dates[Date])
EndOfThisPeriod = LASTDATE(Dates[Date])
 
 
 to find out last year change to this date in my case 01/04/2021-30/08/2021 to 01/04/2020-30/08/2020
 
my date table is like this : image.png
 
image.png
 
It works perfectly fine without a country filter but when I add country filter its shows this error
 
chauhanpooja_0-1630267237184.png

@Anonymous Can you change the relationship cross-filter direction to Single from Both? Also, I would add this column to your financial calendar:

FiscalDayOfYear = 
  VAR __Date = [Date]
  VAR __FinancialYear = [Financial Year]
RETURN
  COUNTROWS(FILTER('Dates',[Financial Year]=__FinancialYear && [Date]<=__Date))


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

chauhanpooja_0-1630333837892.pngchauhanpooja_1-1630333866396.png

both have single connection

 

my main country table : 

 

chauhanpooja_2-1630333920996.png

 

I have added a new column to my date table 

 

chauhanpooja_3-1630334078412.png

 

 

Anonymous
Not applicable

@Greg_Deckler could you please guide me ?

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Kudoed Authors