Forum Discussion
Earliest Date on a slicer does not work
Hello,
I'm attempting a report with multiple years of history for a group of investments, but with one tab specific to year-to-date data. A calendar fact table is set up in conjunction with the investment results, which are then joined on the date column (One to Many). The year to date tab is controlled by a date slicer which is itself filtered to allow the user to select dates within the year, which seems to work fine to filter most date ranges within the period - unless the very first date (Jan 1) is selected, in which case all results end up being displayed and calculated, effectively ignoring the slicer settings.
In lieu of any screenshots, does this sound like a familiar problem to anyone?
Thanks in advance!
6 Replies
- amitchandak
Super User
Not sure I got it, but I am using datesytd or totalytd for ytd with Date as calendar
Year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date])) Last Year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date Filer],-12,MONTH))) Thanks for your response! Perhaps I should have included screenshots and/or been more clear.
Note in the two screenshots below, a slicer is used to restrict the available dates available to the user. In the first screenshot for example, it is set at Jan 14, 2019 to Dec 31, 2019 and the results are as expected. However, in the second screenshot, if the beginning date gets set at the earliest possible value in the slicer, which is Jan 1, 2019, then for some reason it returns ALL the available dates on the fact table, effectively ignoring the slicer. Building the slicer on both the calendar table or the fact table date column has the same effect.
Starting
- amitchandak
I think I saw a similar issue in the past. Typically it happens when dates in calendar table are less than dates in the fact. Check Calendar is coving boundaries.
If did not work . If possible please share a sample pbix file after removing sensitive information.Thanks.
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
v-alq-msftCommunity Support
Hi, Afkkek
It will cause the issue when the date range of Slicer is less than range of fact table. So it is suggested that you can use the following function to create the calendar table, then put the date column into slicer.
Calendar= CALENDARAUTO()
Best Regards,
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ElorianResolver I
Hello,
Not a big help, just to confirm that I faced the same issue with the standard slicer but also with a custom time slicer from the visual repository.
I'm not sure that it is caused by the calendar boundaries or if it's a bug. Maybe putting a filter on the slicer with the wished minimal start date may help.
Kr,
Elorian.
I'm facing the same issue nearing 2023. Did you find a solution?
