locked
Setup default year selection inExcel Power Pivot RRS feed

  • Question

  • Hi,

    We have the requirement to develop a Power Pivot report in excel, we are using Year and Month in the Slicers/Filters  and want to setup the report to run by default forCurrent Year and Current Month considering todays date. Can we set this in the report.

    Thank You,

    Kumar


    Praveen

    Monday, March 31, 2014 4:17 PM

Answers

  • One option is to add calculated 'Is Current Year' and 'Is Current Month' fields to the model and to use these while filtering. say

    = YEAR([MyDateColumn])=YEAR(NOW())

    The values of the column would update every time the model is refreshed.

    • Proposed as answer by Michael Amadi Tuesday, April 1, 2014 5:23 AM
    • Marked as answer by Michael Amadi Friday, June 13, 2014 10:58 PM
    Monday, March 31, 2014 5:50 PM

All replies

  • One option is to add calculated 'Is Current Year' and 'Is Current Month' fields to the model and to use these while filtering. say

    = YEAR([MyDateColumn])=YEAR(NOW())

    The values of the column would update every time the model is refreshed.

    • Proposed as answer by Michael Amadi Tuesday, April 1, 2014 5:23 AM
    • Marked as answer by Michael Amadi Friday, June 13, 2014 10:58 PM
    Monday, March 31, 2014 5:50 PM
  • Hey Rhys,

    one thing I dislike about your solution is the usability in Excel Pivot. Imagine you have a slicer for year and another for month. You now add a third slicer somewhere and filter on "Is Current Data". Normally it will grey out older values in the other slicers.

    If you then click "March" and "2014" you will get no values. You first have to release the filter on "Is Current Data".

    @Kumar: Do you load only valid dates to your datamodel or do you load it till end of year for example?

    Tuesday, April 1, 2014 12:53 PM
  • Hi,

    We load data till end of the year, example we are currently in April 2014, but we have got data upto November 2014. report slicers will have all the months upto November, but want default to be selected as April.

    Thank You.


    Praveen

    Tuesday, April 1, 2014 4:38 PM
  • HI Kumar,

    Perhaps this thread will help: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/2cd10191-9d7d-4d29-b079-e1b3d9a6b6b7/set-default-value-of-slicers?forum=sqlkjpowerpivotforexcel. It appears to be a very similar problem area.


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    Monday, May 12, 2014 7:40 AM