Hi,
I'm trying to use a combination of a few example Excel sheets to build our own Project Management Dashboard,
where our data is a combination of 3 input tables.
In yours I like the scrollbar to be able to scroll through the weeks.
However, we have to present weeks in columns, not the days.
I have seen solutions like in the sheets 'rolling periods' or 'slicer-multi-years' but they process the current date,
and do not act on user-decided start and end range.
I have been experimenting with an extra column in the Date-table that calaculates 'selected' with respect to the (Scrollbar) defined range,
but the calculation is only done on 'refresh', not instantly.
Is there any possibility to make it work?
Regards,
Maarten
Hi Maarten,
Are you able to share your file showing what you've done so far and explain in relation to the file how you'd like it to work?
Mynda
Hi Mynda,
I'm happy to share my current Test-file; see attachment.
In tab Data I have defined cells to control the selected range:
StartWeek, EndWeek and a Range (Bereik; allowing for 16 columns).
In the same tab I created a Date-table (WekenInput) with a formula that makes a number of weeks 'selected',
based on the defined Start- and EndWeek. The table is copied to the Data model.
The tabs Cap, ProjInzet and Real are in fact copies, just showing different parts of the data,
and all three now have a slider to be able to select the range.
Later on these three have to be combined into one overview (probably I need to merge the tables for that),
and then showing Available time, Planned work and Realised Work.
When I now move the slider I have to refresh the query Weken to update the values in the Data model,
in order to get the correct selection of weeks within the columns.
I hope there is a method to create a more responsive way of working.
Maarten
Hi Maarten,
Thanks for sharing your file. The only way to make this more responsive is to move the filtering to Power Pivot rather than the data source. e.g. you could use a Slicer instead of a scroll bar that allows the user to select the periods they want to display. This means all data will be in your model ready for filtering on, rather than excluding data from being loaded as it currently is.
Mynda
Hi Mynda,
Thanks for looking into it. I have tried to use a slicer for the year-week periods, but it keeps a little bit more complex.
For the end-user it is not as easy as ticking the scrollbar to move on a week, he has to select and de-select.
As the data contains weeks for two calendar years the number of periods within the slicer is rather big.
The administration system (source for the data) sees the start of this year as week 53. That week appears at the end of the slicer.
So, I hoped to be able to produce a better interface.
Is there any possibility to control the slicer content by a scrollbar, or so?
Maarten
Hi Maarten,
Have you tried the Timeline Slicer?
Mynda
Hi Mynda,
In fact I rather rejected the Timeline slicer, because you mentioned a few disadvantages of it in one of your example sheets.
But, if there's no other solution, we'll have to use that as a compromis.
Thanks,
Maarten
The only other option is to write some VBA code to handle the filtering, but this wouldn't be as smooth/quick as the timeline slicer.
