Help with Date Slicer - List with multiple year(s) to date

[Nicho248]

Hello Power BI Community - 

I have a date/calendar table setup and all data is tied to this. I'd like to setup a slicer for the user which only allows a few choices (aka a list). This list should present a few pre-determined ranges (2015 to today, 2016 to today, etc up to this year). I need help/guidance on how to setup these ranges to work with the slicer. I've done a few searches and experimented for a while with some of the settings available in a slicer with no luck. 

How do I setup the slicer in this fashion or setup measures/columns to achieve this?

Thanks in advance,

Nick

[AiolosZhao]

Hi @Nicho248 ,

I think you need to tell us what the logic is.

How do you determine the range is 2015 to today or 2016 to today etc?

Thanks.

Aiolos Zhao

[Nicho248]

Hello @AiolosZhao 

Thank you for taking a look into this.

I am not sure how to setup the report to use the date range effectively. I tried so far using a slicer and a simple calendar table (where each day is listed). I am not able to use any combo of ranges in a standard slicer as far as I can tell through this method. I have now added calculated columns based on this date. One for each year to date. The 2017YTD formula is =IF(DATE>=DATE(2017/1/1),"YES",""). This grants me 4 fields to add to a slicer or chart, however, these didn't work. I have switched from a slicer to a bar chart or treemap, but these didn't work either.

Any help would be appreciated.

N

[AiolosZhao]

Hi @Nicho248 ,

Please tell me,

1. If you want to restrict the slicer, for example, the calendar date has 2015-2020, but what you want is 2017-2020. Then you can use the "filter pane" to un-check the 2015 and 2016, then you made it.

2. If you want to restrict the bar chart or other charts, for example, the calendar date has 2015-2020, but what you want is 2017-2020.

Then you need to restrict it in your measure, but not dimension.

For example, you have a measure = sum(value)

Then you change it to measure = calculate(sum(value), filter(your_table, year(your_date) >= 2017))

You will get the result that you want.

3. If the above is not what you want, give me the samples and the desired result.

Aiolos Zhao

[Nicho248]

Hello @AiolosZhao 

Yes, I understand that a user can manually select the custom date range in a slicer. I am wanting to make it as simple as possibly by making it a pre-defined choice. How would I do that? If not in a slicer, then maybe by a chart or other means would be sufficient as well. Anything that would allow a user to click a button or object in power bi and it would change all affected graphs/charts/tables based on that criteria. My hard part is getting lost how to make multiple choices for different date ranges which overlap. I'd upload something but I don't think it would add value to the conversation. Its a simple date table, any random other data will do. I've made no progress just numerous failed attempts. I've uploaded a simple ms paint. Hopefully this will get the point across. I'd don't know how to setup the dax code in this instance (if dax is the solution here).

E.G.

2017-1-1 to today

2018-1-1 to today

2019-1-1 to today

2020-1-1 to today.

N

[AiolosZhao]

Hi @Nicho248 ,

How about using a slicer with "after" mode?

[Nicho248]

Hi @AiolosZhao -

Yes, but the trouble is with the next slicer. It'll have conflict with the first one.

N

[v-jingzhang]

@Nicho248 How about using Year in the slicer and choosing Greater than or equal to?

[Nicho248]

Hello @v-jingzhang 

No, I'm not looking for the user to do any data entry. If there is only one slicer then they would have to change it manually. 

N