I have data which shows completion dates of training modules by department (see tab = Raw Data in the attached)
I need to create a chart which will allow slicers to show individual modules and/or individual departments.
I have transformed the data in Power Query to unpivot the module columns and have created a Pivot table which counts up the number of completions by date. I have also created a chart to show the information and included 2 slicers which work fine (see tab = Table1 in the attached).
I have 2 issues;
- I am not able to set min/max dates as bounds or set the required date interval as the x-axis will not show as dates.
- I need to add another series which will show the deadline for completion
I believe it's all because the data source for the chart & slicers is a Pivot table and not a 'normal' table. However, there are 6 departments & 5 modules and I don't want to create 30 separate tables & charts and have to update them every week.
There is an example of what I want on tab = Example).
Can you advise how I can fix this issue or suggest an alternative which will do what I need?
Many thanks in advance
Which version (and build, if 365) of Office do you have?
Hi - I have Microsoft 365 Apps for enterprise.
Build 16.0.17126.20132) 32-bit
To simplify the requirement, I would be happy to create charts for each module and just have a slicer to filter by department. As each module has a different deadline that may be easier.
You can add a TimeLine to the pivot chart to control the dates displayed on the X-axis. And to include the deadline marker you need to create a table with all relevant deadlines and quantities (by department, by module) and append it to the Table1 query. Now you can make a combo chart as demonstrated in the attached file.
Thank you Riny - that does help. However, the axis still isn't in date format, so the Labels are inconsistent and I can't set them to a proper interval i.e. 7 days.
I note that you have also filtered the list of dates to end in June. However, each of the modules has different deadlines, so how could that be automated?
Is there a method I can use to 'trick' Excel into thinking they are dates, or do I need to create a custom grouping of dates and select that as the axis? Any help much appreciated
I am afraid I'll have to leave it to someone else.
