locked
Alternative for Timeline Slicer RRS feed

  • Question

  • At the moment power BI does not have a Timeline Slicer (for date,month, quarter, year). Is there a quick and easy workaround?

    I would like to add a filter (or slicer) on a lot of report pages, so I can only see the sales for the last quarter, month, etc. Sort of like a checkbox function or the Time Slicer in Excel. 

    If I add a column, e.g. "is in time period: last quarter", I would have to add additional columns for last month, year. Then it would be possible to add a Slicer for each of these columns. Is there a quicker and more elegant solution?

    Monday, July 13, 2015 12:16 PM

Answers

  • Couple different options but I would recommend subscribing to Boyan Penev’s free DateStream feed. This is essentially a table of dates that is useful for time intelligence type stuff. Won’t help you in Power View since it doesn’t seem to support time intelligence features but if you want to play around with time intelligence you can use standard Pivot Tables and Pivot Charts.

    That being said, if you link all of your dates to the DateStream feed, you should be able to create a series of slicers for month, quarter, year.

    Also, I have also taken the data feed with a date in it and created the following calculated columns:

    Created On

    Puts the created on date/time into a standard format for use with a time intelligence table

    =DATE(YEAR([CreatedOn]),MONTH([CreatedOn]),DAY([CreatedOn]))

    Created Year

    For wiring to a Year table

    =YEAR([Created On])

    Created Month

    For wiring to a Month table

    =FORMAT([Created On],"mmmm")

    Created Month Sort

    Sort field for Created Month

    =MONTH([Created On])

    Created Quarter

    For wiring up to a Quarters table

    =IF([Created Month Sort]<4,"Q1",IF([Created Month Sort]<7,"Q2",IF([Created Month Sort]<10,"Q3","Q4")))

    You can either have a Year table and Month table, etc. or create slicers directly off of these calculated columns. Depends on your specific scenario.


    Monday, July 13, 2015 12:45 PM

All replies

  • Couple different options but I would recommend subscribing to Boyan Penev’s free DateStream feed. This is essentially a table of dates that is useful for time intelligence type stuff. Won’t help you in Power View since it doesn’t seem to support time intelligence features but if you want to play around with time intelligence you can use standard Pivot Tables and Pivot Charts.

    That being said, if you link all of your dates to the DateStream feed, you should be able to create a series of slicers for month, quarter, year.

    Also, I have also taken the data feed with a date in it and created the following calculated columns:

    Created On

    Puts the created on date/time into a standard format for use with a time intelligence table

    =DATE(YEAR([CreatedOn]),MONTH([CreatedOn]),DAY([CreatedOn]))

    Created Year

    For wiring to a Year table

    =YEAR([Created On])

    Created Month

    For wiring to a Month table

    =FORMAT([Created On],"mmmm")

    Created Month Sort

    Sort field for Created Month

    =MONTH([Created On])

    Created Quarter

    For wiring up to a Quarters table

    =IF([Created Month Sort]<4,"Q1",IF([Created Month Sort]<7,"Q2",IF([Created Month Sort]<10,"Q3","Q4")))

    You can either have a Year table and Month table, etc. or create slicers directly off of these calculated columns. Depends on your specific scenario.


    Monday, July 13, 2015 12:45 PM
  • Hi Seth,

    thanks a lot for your help. I tried out the DataStream Feed and am pretty sure, I will be able to use it for other cases. And actually for "quarter of year" it worked great. Although, I also like the IF condition. And also added another IF condition to check if sales were in the last 90 days. Perfect.

    My problem was that I wasn't able to use the slicer for these calculated columns with numeric values, since the value needs to be on the axis for the slicer to work. It worked fine with the formatted month, etc. But I got that figured out now.

    Thanks again for your help!

    Tuesday, July 14, 2015 2:41 PM