Is it possible to create dynamic pivot table filters based on day of week?

[Magnatolia]

Hi guys,

I have a personal task tracker which has about 100 tasks. I decided to spend 5 mins a night updating a planning column for the next day so I can look at a flat pivot table of tasks I've allocated for the day along with an estimate planned completion e.g. Planned (20%) which tells me to complete at least 20% of this task on that day.

What I want to do is create a separate standing tasks list that will combine into the pivot table. Both tables will have the same layout except for the standing one which I'm planning to have one extra column at the end for timing i.e. daily, 1/week, 3/week, monthly etc.

I've never used Get & Transform, but thinking I can create a Query to combine the two tables together into one table that I can then use for the PowerPivot. Please let me know if there's a better way to do this, or if my option won't work.

Second, I want to know if it's possible to apply a dynamic filter for the day of the week. For example, I am planning to go to the gym at work Mondays and Thursdays so this would be a standing task with timing of 'Monday' and a second for 'Thursday'. I would potentially have a third table set up for specific timings for the days of the week. Can a filter be applied to this data set to only show tasks from this table that have today's day name, i.e. if I was running the pivot on a Monday it would not show anything for the other days from that table. It would still show everything from the generic second table and the first table. Hope that makes sense?

Thanks!

 

[istiasztalos]

Hi,

Why not have just one table? you can have bot recurring and one time tasks listed, if you want recurring tasks separated you can sort them to the top using your extra column.

For always viewing the current day's tasks, I'd add another extra column where a formula would check if that task is applicable today or not (using WEEKDAY) and return TRUE/FALSE; then your pivot table can filtered to show TRUE only (ad-hoc ones could have also TRUE in the 2nd extra column)