Forum Discussion

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

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?

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

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])
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 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
      Icon for Community Champion rankCommunity Champion
      5 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
        5 years ago

        both have single connection

         

        my main country table : 

         

         

        I have added a new column to my date table