Help with Interactive Dashboard - Show both current and previous months data (Newbie)

[smedleyuk]

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

 

[oldbrewer]

col d

col e

col f

row 5

D9

E9

F9

4

5

6

D

E

F

MONTH

kpi1

kpi2

kpi3

APR

MAR

row 9

DEPT1

JAN

80.0

77.0

44.0

9

AUG

JUL

DEPT2

JAN

87.0

79.0

47.0

DEC

NOV

DEPT3

JAN

82.0

82.0

46.0

FEB

JAN

DEPT1

FEB

82.4

79.3

45.3

JAN

DEC

DEPT2

FEB

89.6

81.4

48.4

JUL

JUN

DEPT3

FEB

84.5

84.5

47.4

JUN

MAY

DEPT1

MAR

85.0

82.0

47.0

MAR

FEB

DEPT2

MAR

93.0

84.0

51.0

MAY

APR

row 17

DEPT3

MAR

88.0

88.0

50.0

17

NOV

OCT

OCT

SEP

row 19

D17

E17

F17

SEP

AUG

enter dept name

DEPT2

4

D

enter month

MAR

5

E

prev month

FEB

6

F

the dept name and selected month

are pulled from user input area

kpi1

D9

D17

kpi2

E9

E17

DEPT2

FEB

MAR

CHANGE %

kpi3

F9

F17

kpi1

89.6

93.0

3.78

kpi2

81.4

84.0

3.23

kpi3

48.4

51.0

5.35

once some helper reference tables are set up on the right

you can pull any info totally automatically from the main data table

in the table above 89.6 is derived from

=SUMPRODUCT(($B$9:$B$17=$B$30)*($C$9:$C$17=C$30)*(INDIRECT(VLOOKUP($B31,kpitable,2)):INDIRECT(VLOOKUP($B31,kpitable,3))))

<colgroup><col><col span="14"></colgroup><tbody>
</tbody>