Forum Discussion
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:
I tried to follow the following link (also tried several things i.e.
) 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
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/434008Dear 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])
