We’ve all been there. Our shiny new dashboard or report pack is finished and ready to go meet its users. We’ve presented the key information clearly, we’ve followed all the data vis guidelines on effective charting and use of colour. We like it a lot, and we’ve thought ahead and built in lots of flexibility with slicers and managed drill paths so it can already help answer some of the questions it will doubtless raise.
It’s a little disappointing then that one of the first pieces of feedback is that the senior execs don’t actually want to use the interactivity much. They want to open the dashboard and see the current month picture (or previous month), and don’t want to waste valuable seconds selecting the month in a slicer…
Joking aside, in lots of situations it’s a sensible request, and there are various different ways to handle it in an XLCubed / Analysis Services world. Often this will be for a multi-sheet report incorporating grids, formulae, and charting elements and we need a centralised point to handle the month selection – enter the XL3MemberNavigate() formula.
This lets you pick a hierarchy and a level, and you can specify that you want the Last member (first and previous / next are also available). It’s available in the XLCubed Insert Formula dialog. In our case we’d pick the date hierarchy, the month level and choose Last, generating a formula as below:
=XL3MemberNavigate(1,”[Date].[Calendar]”,”[Date].[Calendar].[Month]”,”LastMember”)
The issue is that at this point it has no concept of data, it will give you the last month available in the hierarchy, not the last month with data. However that’s just another parameter away, we can add dimension member pairs to force a data check, as below, where we are checking that data exists for the “Reseller Sales Amount” measure.
=XL3MemberNavigate(1,”[Date].[Calendar]”,”[Date].[Calendar].[Month]”,”LastMember”,0,”[Measures]”,”Reseller Sales Amount”)
So that will return the last month with data, and as we know in XLCubed all grids, formulae and XLCubed charts can be based off a cell. The Xl3MemberNavigate() cell becomes the driver for all time selections in the report. Job done. Or is it? What if you actually wanted the last complete month? :
=XL3MemberNavigate(1,”[Date].[Calendar]”,”[Date].[Calendar].[Month]”,”LastMember”,2,“[Measures]”,”Reseller Sales Amount”)
Adding the addional ‘2’ parameter means it will go back an additional month, hence giving you the last completed month.
In our experience this is far and away the easiest way to handle current or Previous month reporting, and we hope you find it useful if it’s new to you. For more information on XL3MemberNavigate check our wiki.