I have a date slicer set to show a subset of dates from our Main Date table. The Date table holds many years of dates and i want my slicer to look neat. As such, I added an extra field to the date table called "Current". The value in this field is either Y or N. The range of dates i want to show, the Current Dates, spread from 183 days ago up to 720 days in the future. All up, its a subset of 912 (ish) days from the main date table.
above, my current dates are 7/2/2024 through to 6/8/2026.
Moving the selector changes the dates displayed in the boxes above the line.
Great.
Now, one of my users wants to be able to toggle between this "current" view of activity (projects that are active during Current weeks) and a wider view which shows activity that has occurred on all projects from our earliest project start date to activities on our longest planned activities. The Date table in its entirety has 75,000 rows so simply removing the filter for the Current projects will cause a mess.
At this point, i will introduce a second table to my question. First was the Date table and the new one is call Projects. Projects contains project date including a field each for Start Date and End Date. So, to limit the number of dates that would appear on the date slicer, I thought i would define the earliest start date from the Projects Table and also the latest End Date.
I referenced the Projects table, selected the Start column, removed all other columns, and inserted the following in the formula bar: = List.Min(Source[Activity Start]). The result is the earliest start date of our projects. I renamed the table to "TblActivityStart" 20/12/2020
I repeated this step to create the "TblActivityEnd" table. changed the formula to : = List.Max(Source[Activity End]) 1/1/2050
God, I hope someone is still reading at this point.
I then removed all dates from the Date table that were before the TblActivityStart date and after the TblActivityEnd date by adding another column to the Date table with an IF statement. If outside of the Start and End Date N, other wise Y. then filtered for Y and finally removed this new column. The end result is the Date table plus the Current column with only 10000 days (rather than 75,000 at the start).
BUT now the date slicer from above just doesn't work properly. In the original set up, the page is filtered for Current with the earliest date shown 7/2/2024 and the latest date being 6/8/2026.
if the filter is removed or changed to N, these dates change to TblActivityStart (30/12/2020) and TblActivityEnd (1/1/2050) as they should.
And thats where the stay. No matter whether the slider on the slicer is moved, or the page filter cleared, those dates no longer change.
What have i done wrong? I don't think i can upload a dummy version because the tables come from our EDW. I'll try and create a standalone small version which does the same thing and upload it. In the meantime, i would greatly appreciate any help that comes my way.
above, my current dates are 7/2/2024 through to 6/8/2026.
Moving the selector changes the dates displayed in the boxes above the line.
Great.
Now, one of my users wants to be able to toggle between this "current" view of activity (projects that are active during Current weeks) and a wider view which shows activity that has occurred on all projects from our earliest project start date to activities on our longest planned activities. The Date table in its entirety has 75,000 rows so simply removing the filter for the Current projects will cause a mess.
At this point, i will introduce a second table to my question. First was the Date table and the new one is call Projects. Projects contains project date including a field each for Start Date and End Date. So, to limit the number of dates that would appear on the date slicer, I thought i would define the earliest start date from the Projects Table and also the latest End Date.
I referenced the Projects table, selected the Start column, removed all other columns, and inserted the following in the formula bar: = List.Min(Source[Activity Start]). The result is the earliest start date of our projects. I renamed the table to "TblActivityStart" 20/12/2020
I repeated this step to create the "TblActivityEnd" table. changed the formula to : = List.Max(Source[Activity End]) 1/1/2050
God, I hope someone is still reading at this point.
I then removed all dates from the Date table that were before the TblActivityStart date and after the TblActivityEnd date by adding another column to the Date table with an IF statement. If outside of the Start and End Date N, other wise Y. then filtered for Y and finally removed this new column. The end result is the Date table plus the Current column with only 10000 days (rather than 75,000 at the start).
BUT now the date slicer from above just doesn't work properly. In the original set up, the page is filtered for Current with the earliest date shown 7/2/2024 and the latest date being 6/8/2026.
if the filter is removed or changed to N, these dates change to TblActivityStart (30/12/2020) and TblActivityEnd (1/1/2050) as they should.
And thats where the stay. No matter whether the slider on the slicer is moved, or the page filter cleared, those dates no longer change.
What have i done wrong? I don't think i can upload a dummy version because the tables come from our EDW. I'll try and create a standalone small version which does the same thing and upload it. In the meantime, i would greatly appreciate any help that comes my way.
