Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nicho248
Helper II
Helper II

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

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

8 REPLIES 8
AiolosZhao
Memorable Member
Memorable Member

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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).pic.png

 

E.G.

2017-1-1 to today

2018-1-1 to today

2019-1-1 to today

2020-1-1 to today.

 

N

Hi @Nicho248 ,

 

How about using a slicer with "after" mode?

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @AiolosZhao -

 

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

 

N

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.