Hi,
As Excel 2010 Power Pivot does not have the timeline slicer function, is there a way to work around this?
I have data tables from 2011 to current date and want to show the previous years totals (Jan-Dec) compared to the 2017 YTD (Jan - Nov) in my pivot tables.
For example, using a year slicer with a month slicer, the results compare previous year Jan-Nov with current year Jan-Nov.
I've tried using a slicer using a date integer column - see attached picture, which does work. However, the potential for selecting an incorrect range could be a problem for inexperienced users of the file.
Any ideas are appreciated!
Carolyn
Hi Carolyn,
You need to write a DAX measure that calculates the prior period values in context of the period selected in the Slicer.
See Power Pivot course session 10 for Time Intelligence Functions. In particular 10.01, 10.02 and 10.08, but of course it wouldn't hurt to watch all of the tutorials in that session because you never know when you might need the others, or if one of them might be more suitable.
If you get stuck please come back with your measure attempt and we can help from there.
Mynda
Hi Mynda,
I went through session 10.1 to 10.8. Thank you for pointing me in the right direction.
My goal is to create a chart showing the YTD totals for 2011 - 2016 and then slice the 2017 by month. I was able to achieve the desired chart by creating two pivot tables and combining them into one manual table for the chart. See attached screen shot of my test file.
I used the DAX formula REC YTD:=TOTALYTD([Recordables],'Date'[Date])
The slicer by month for the current year is important as the source data table is updated daily and the reports are done after the end of each month - combined with other data in the dashboard.
Is this the manual table the only method I can use in excel 2010 to achieve a "timeline" slicer for this type of chart?
As always, thanks for your help! It is greatly appreciated.
Carolyn
Hi Carolyn,
How about a compromise where you show the total YTD irrespective of the Slicer selection and then you also show the values for each year for the selected months like this:
See Excel file attached with the following DAX measures:
REC ALL: =CALCULATE(SUM(Table1[Recordables]),ALL('Date'[Month]))
REC Selected Months: =TOTALYTD(SUM(Table1[Recordables]),'Date'[Date])
Note: you don't need a year slicer since you want to show all years.
Mynda
Hi Mynda,
Thank you for your suggestions. It's been great having MOTH as resource for training and the forum for support.
Wishing you all a Merry Christmas and prosperous New Year!
Carolyn
My pleasure, Carolyn. Best wishes to you too for Christmas and 2018!
Mynda
