This is my first post, so Hi everyone!! I have been using this forum for a few weeks but have never asked a question so here goes.
I have been tasked with creating an interactive dashboard for my organisation, which covers workforce data. I want to create a table of KPI's, which updates depending on the month selected. Now I don't have a problem with this as I am quite familiar with pivot tables and slicers, but there is a particular column I need to include which has got me stuck.
Basically the table is arranged so that rows reference the KPI metric and columns reference the department name. As well as department name, I want to include a column for the overall figure (org wide) and in next column showi the same KPI's, but for the previous month i.e. to show trend.
I'm ok with the overall figure for the month selected, however I am not sure what I need to do in Excel to show the data for the previous month, and how this could be updated depending on the months selected. For example, when December is selected you would see the overall figure for December and in the adjacent column the overall figure for November.
My source data is coming from a table, and I plan to use pivot tables and formula to calculate the KPI. The actual KPI Figures do not appear in my source data. For example I have a column showing total staff in post (SIP), and another showing agreed budget (WTE). Using these I can then use formula to show Vacancy %. KPI
Anyway, I am yet to build the dashboard so any pointers or suggestions about how I might be able to show the current and previous month in the same table, as well as being able to refresh this data depending on the month selected would be really helpful as I'm a bit lost on this one.
Looking forward to hearing from you
Craig
I have been tasked with creating an interactive dashboard for my organisation, which covers workforce data. I want to create a table of KPI's, which updates depending on the month selected. Now I don't have a problem with this as I am quite familiar with pivot tables and slicers, but there is a particular column I need to include which has got me stuck.
Basically the table is arranged so that rows reference the KPI metric and columns reference the department name. As well as department name, I want to include a column for the overall figure (org wide) and in next column showi the same KPI's, but for the previous month i.e. to show trend.
I'm ok with the overall figure for the month selected, however I am not sure what I need to do in Excel to show the data for the previous month, and how this could be updated depending on the months selected. For example, when December is selected you would see the overall figure for December and in the adjacent column the overall figure for November.
My source data is coming from a table, and I plan to use pivot tables and formula to calculate the KPI. The actual KPI Figures do not appear in my source data. For example I have a column showing total staff in post (SIP), and another showing agreed budget (WTE). Using these I can then use formula to show Vacancy %. KPI
Anyway, I am yet to build the dashboard so any pointers or suggestions about how I might be able to show the current and previous month in the same table, as well as being able to refresh this data depending on the month selected would be really helpful as I'm a bit lost on this one.
Looking forward to hearing from you
Craig
