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
Aakash_Ladi
Regular Visitor

Can I set slicer values by default to last to last 365 days dynamically based on another slicer?

I have two slicers- One for date range selection of This Year measures and another one for date range selection of Last Year. I want to set them dynamically as below:

This year slicer- Set this as last 365 days, which is quite easy and can be done by applying the filter in the filter pane as Relative Date --> in the last 365 days

Last Year slicer- This should be something like- set the end date as one day prior to the start date of This Year slicer and set the start date as 365 days back from the end date of last year slicer

 

Do you think there is any possible way of doing this?

 

4 REPLIES 4
v-jianboli-msft
Community Support
Community Support

Hi @Aakash_Ladi ,

 

To my knowledge, if you choose to use a slicer of type Between, you cannot dynamically modify the range of the slicer in this way. Unfortunately, there is no better way to do this than manually selecting or switching to a list type slicer. Maybe you can choose the list type slicer and add a select all button to it.

An idea similar with what you expect has been submitted in Microsoft Idea (powerbi.com), you can add comments and vote it up. 

If a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

 

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Aakash_Ladi
Regular Visitor

Hi,

 

Actually the problem is This year slicer would be a range too. It would not be a single value. So, it is a "Between" slicer for both This year and last year. I want something like this-

This year slicer - set to last 365 days  (for example- for today, the values will be 2021-12-30 to 2022-12-29)

Aakash_Ladi_0-1672387120791.png

 

So in this case Last year slicer should be dynamically set to the range of 365 days before the start date of This Year Slicer(i.e.  2020-12-30 to 2021-12-29)

 

I think I was not clear in the question above and I apologize for that

 

 

v-jianboli-msft
Community Support
Community Support

Hi @Aakash_Ladi ,

 

Please try:

First create a new table for slicer:

vjianbolimsft_0-1670550665332.png

Then create a new slicer for Last Year slicer and apply the measure to the slicer's filter:

Measure = 
var _a = SELECTEDVALUE('Date'[Date])
return IF(MAX('For prior year slicer'[Date])<_a&&MAX('For prior year slicer'[Date])>=_a-365,1,0)

vjianbolimsft_1-1670550743422.png

Final output:

vjianbolimsft_2-1670550801786.png

 

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-jianboli-msft,

 

I tried doing something with your suggestion and my scenario. Since I need to set a "Between" slicer in this case, I did this-

I created two measures MinPP and MaxPP which should be acting as starting and ending date in the slicer.

Then I added a measure as you suggested with slight modification- 

Aakash_Ladi_0-1672730659051.png

 

Then I added a filter on the slicer for filterCondition = 1, but then it is setting the endDate correctly but not the startDate. It takes MaxPP as the endDate but It won't take MinPP as StartDate

Aakash_Ladi_1-1672730751944.png

 

 

second slicer is the one which should be set to 12/2/2021 to 12/30/2021. 

 

I can't think of any reason why this is happening. Could you try and explain what is wrong here?

 

Regards

 

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.