locked
Excel > Power Pivot > Slicers > How to set the default filter value? RRS feed

  • Question

  • Hello Friends,

    I want to understand how can we set the default filter value to any Power Pivot slicer in excel?

    Also, what is the easiest way to filter data based on a date for current year & previous year. For e.g., I want to allow my users to select a date period - FromDate & ToDate using slicers, and based on that selection, I want to show sales details of the selected period in one column & exact last year in another column.

    Please guide.

    Thanks,

    Vinay


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Tuesday, September 15, 2015 8:16 AM

Answers

  • Hi Vinay,

    The thing with slicers is that with them, you set a selection directly on a column in your model. So you cannot have two slicers on the same column.

    The general concept in Power Pivot models with date-related data is to have a Date table with a relationship from your fact table to the Date table (so in your example, from Sales_Data[TransactionDate] to Date[Date]. But if you want to have two slicers on dates working independently, you can only do this using two separate tables. The alternative would be to have one slicer on the date table (this can be one on year and one on month, of course) and another slicer for selection of period length as I wrote in my first reply.

    The DATESBETWEEN function returns a list of dates from the Date table (the first argument) starting from a certain date (the second argument) and ending at another date (the third argument). The slicers on the (first) Date table, how many that may be, define a selection on this table and my assumption is that a user would expect the selected year and month to be included in the period to report. To include the selected period, we need to take the first date of the selected period as our starting point in DATESBETWEEN, hence the MIN() function. The same holds for the last date, now using MAX().

    Restricting the possible selection in the slicer is another topic. You cannot enforce this in the Excel GUI, but you could decide to not return any result when multiple selection is made. This is not really necessary, though, because we use the MIN and MAX: if I select two months in the DateFrom slicers, both months will be included because we start from the first date in the first month. Any month in between them would also be reported.

    (But if you really want to, make sure you have a column which should have only one value selected, e.g. YearMonth like 'August, 2015' and precede the calculation with IF(HASONEVALUE(DateFrom[YearMonth]),...)

    Now that we're on this, why not use a timeline (in Excel 2013)? With this, you would only need a date column and all the rest is taken care of by Excel.

    Hope this helps.

    Michiel

    • Marked as answer by vinaypugalia Wednesday, September 16, 2015 4:11 AM
    Tuesday, September 15, 2015 2:52 PM
    Answerer

All replies

  • Hi Vinay,

    There is no such thing as a default filter value for a slicer. Instead, the selection you made when saving the Excel file is the one that will be there when the file is opened.

    About the date selection, if you want to have two separate slicers with dates, you'll need two separate date columns (in different tables to avoid one slicer to be filtered through the other one). I don't think this is what you really want, and you probably don't want to have dates in a slicer either; instead you could have a slicer for year, and another for month or week. You could have another slicer with some options like 'This month', 'Last two months', 'Last three months' etcetera. These options should be in a separate table.

    Does this make sense for you?

    Tuesday, September 15, 2015 8:43 AM
    Answerer
  • Hello Michiel,

    Thanks for clarifying that there is NO way/concept to set the default filter value for the slicer.

    Yes, you are correct that I will have to have separate slicers for Year & Month for FromDate & ToDate selections. It makes sense.

    However, could you please suggest me a logic by which I can pick the last period's details & current period's details based on the slicer selection?

    Thanks,

    Vinay


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Tuesday, September 15, 2015 10:16 AM
  • So the concept is that you'll have two Date tables to take the slicers from, right?

    Let's say these are DateFrom and DateTo. As you're not selecting a single date but rather a period of time in both tables (e.g. a month), we need to assume that we want to report from the start of the period selected in DateFrom up until the end of the period selected in DateTo. You get these dates with MIN(DateFrom[Date]) and MAX(DateTo[Date]), respectively.

    If [Sales] is your base calculation for sales (e.g. SUM(Sales[Amount]) ) then sales for the selected period is

    SalesSelected:=CALCULATE([Sales],DATESBETWEEN(DateFrom[Date],MIN(DateFrom[Date]),MAX(DateTo[Date])))

    (make sure all dates in DateTo are also in DateFrom). This assumes a relationship from Sales to DateFrom table, and DateFrom table to be the date table of your model.

    The sales for the previous year with the same selection would be

    SalesSelectedLastYear:=CALCULATE([SalesSelected],SAMEPERIODLASTYEAR(DateFrom[Date]))

    Tuesday, September 15, 2015 11:22 AM
    Answerer
  • Hello Michiel,

    Thanks for the detailed reply. However, being new to this, I fail to understand your suggestion completely. I am really sorry for this.

    Below are few queries based on my understanding - 

    Say, I have only 1 table model in my SSAS - "Sales_Data", with columns - TransactionDate, CustomerName, SalesExecutive, Region, SaleAmount.

    And my expected output is - CustomerName, TotalSalesAmount(selected period), TotalSalesAmount(same period last year), DifferenceAmount, Diff%

    With the above situation, I am unable to map your concept of having 2 dates tables.

    Also, I do not understand why did you use MIN() & MAX() functions? Is this to counter the multiple selection?? Also, is there any way to restrict user to select single Month & single Year for "From Date" & "To Date".

    "SelectedSales" & "SalesSelectedLastYear" are the measure I suppose which you have created. Right?

    Hope, I was able to express my queries well.

    Thanks a lot for your replies. They have really given me a good direction.

    Thanks again!

    -Vinay


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Tuesday, September 15, 2015 12:51 PM
  • Hi Vinay,

    The thing with slicers is that with them, you set a selection directly on a column in your model. So you cannot have two slicers on the same column.

    The general concept in Power Pivot models with date-related data is to have a Date table with a relationship from your fact table to the Date table (so in your example, from Sales_Data[TransactionDate] to Date[Date]. But if you want to have two slicers on dates working independently, you can only do this using two separate tables. The alternative would be to have one slicer on the date table (this can be one on year and one on month, of course) and another slicer for selection of period length as I wrote in my first reply.

    The DATESBETWEEN function returns a list of dates from the Date table (the first argument) starting from a certain date (the second argument) and ending at another date (the third argument). The slicers on the (first) Date table, how many that may be, define a selection on this table and my assumption is that a user would expect the selected year and month to be included in the period to report. To include the selected period, we need to take the first date of the selected period as our starting point in DATESBETWEEN, hence the MIN() function. The same holds for the last date, now using MAX().

    Restricting the possible selection in the slicer is another topic. You cannot enforce this in the Excel GUI, but you could decide to not return any result when multiple selection is made. This is not really necessary, though, because we use the MIN and MAX: if I select two months in the DateFrom slicers, both months will be included because we start from the first date in the first month. Any month in between them would also be reported.

    (But if you really want to, make sure you have a column which should have only one value selected, e.g. YearMonth like 'August, 2015' and precede the calculation with IF(HASONEVALUE(DateFrom[YearMonth]),...)

    Now that we're on this, why not use a timeline (in Excel 2013)? With this, you would only need a date column and all the rest is taken care of by Excel.

    Hope this helps.

    Michiel

    • Marked as answer by vinaypugalia Wednesday, September 16, 2015 4:11 AM
    Tuesday, September 15, 2015 2:52 PM
    Answerer
  • Hello Michiel,

    This was a really good explanation.

    I will try this & will get back to you in case I have any further queries.

    Thanks,

    Vinay


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Wednesday, September 16, 2015 4:11 AM
  • Hello Michiel,

    I have implemented the solution as under - 

    1. A table have series of dates from 2013 till 2015 (date_data). This is defined as the date table of my Model.

    2. A fact table having the sales data. This table has got data from 2010 till date.

    3. A relation has been set between - 'sales_data'[TransactionDate] & 'date_data'[FilterDate]

    4. I then defined 2 measures on the 'sales_data' as under - 

    TotalIncome:=SUM('sales_data'[Income])

    TotalIncome_PreviousYear:=CALCULATE([Total Income],SAMEPERIODLASTYEAR('date_data'[FilterDate]))

    Thing works as expected if I filter the details for 2014 or 2015. But when I filter the records on 2013, I do not see data for Previous Year even though the sales_data table has got the previous year data.

    Could you please guide me where I went wrong?

    Thanks,

    Vinay


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Friday, September 18, 2015 4:34 PM
  • hi Vinay,

    you're almost there :-)

    The date functions take the date column as an argument, and don't do anything beyond that. So the reason you don't get Previous Year results in 2013 is that you don't have 2012 dates in your date table.

    The solution is simple: extend your date table to include all dates in your fact table, so in your case from 2010 onwards (you'll probably have a 'blank' year in your reports as well, extending your date table will solve that as well). Make sure not to have gaps in your date table, though; every date in the range needed should be in there.

    regards,

    Michiel

    Monday, September 21, 2015 6:53 AM
    Answerer
  • Hello Michiel,

    I thought this might be the issue but still wanted to confirm with you.

    So, what I have understood is - SAMEPERIODLASTYEAR() will return the table of dates from the date table itself (in this case 'date_data'[FilterDate]). So, if the data_data table has only 1 date of last year in it, it will return only 1 date.

    Please confirm.

    Could you please also explain the "Blank Year" you are talking about?

    Thanks,

    Vinay


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Monday, September 21, 2015 10:03 AM
  • Hi Vinay,

    that's exactly how it works, you understood that correctly.

    About the blank year: when your sales data table contains a date that is not in your date table (while you have defined a relationship on that column), Power Pivot adds a blank row to the date table where all non-existent values land. You will not see this blank row in your model, but you do see it in your reports.

    So when, as you stated, your sales table has data for 2012 but your date table doesn't, all 2012 results (your [TotalIncome] measure) will be reported on a 'blank' label.

    Monday, September 28, 2015 8:51 AM
    Answerer
  • Hey Michiel,

    Thanks a lot for your explanation & guidance.

    I have now extended my date table to cover all the dates from the fact table.

    Also, can you please look into this link and share your views.

    Thanks again!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia


    Monday, September 28, 2015 4:09 PM
  • Hello Michiel,

    I see one issue in my implementation.

    Now, both the Fact Table & Date Table has got data for the period 2010 to 2015.

    Hence, the Timeline will allow user to select a 2010 month/quarter/date.

    But if he does so, he will not be able to see the "Same Period last Year" details as neither the Date table has got that data nor the fact table.

    Now, if I extend the Data table to have data from 2009, to ensure the dates of the last period will be picked, I will also have to extend the date period in the fact table as well to ensure the data is also picked. And if I do this, I will again fall in the same trap - "Now if user selects 2009...what??"

    Is there any way by which we can restrict the TimeLine to show only data from (in our case) 2010 to 2015 even though it has got dates from 2009, so that user will not be able to filter for 2009 period but we will still get it from SAMEPERIODLASTYEAR() as the date table has got those values?

    Please guide.

    Thanks!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Tuesday, September 29, 2015 4:14 AM