Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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-...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 : 
 
 
It works perfectly fine without a country filter but when I add country filter its shows this error
 
Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

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))
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    both have single connection

     

    my main country table : 

     

     

    I have added a new column to my date table 

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Greg_Deckler could you please guide me ?