Forum Discussion
Between slicer issue while capturing Min and Max values on Date column (is it known issue??)
Hi Folks,
I am not sure whether its known issue or not. I have a report where I have a slicer on date which allows user to select date for a range. When I try to capture Min and Max of selected date its nowt working....Where as if I change this slicer type to List I am able to capture Min and Max date as expected.
Is it a known issue while using Range on Dates?? can you please suggest some work around to capture these dates dynamically while using Between. Please see the attached PBIX for reference.
Hi itsmebvk,
Firstly, you can't create a "calculated column" based on a Sclier visual. Because a calculated column can't respond to the slicers.
Can you share a snapshot, please? I can't reproduce your example in your file. Please refer to the snapshot below.
Best Regards,
That's OK. I'm glad you found the root cause and solved this issue.
Best Regards,
15 Replies
Hi Folks, anyone faced this issue??
this is a tricky step because the min and max from the slicer changes as you start adding fields to display your data. for example, I want to calculate the cost rate of all days in August that is MIN=08/01/2022 MAX=08/31/2022 and it works fine but if I want to display this baseline cost per team then the MAX date changes because the latest date I have record for any team is 08/21/2022 so my calculation is not the full month but from 08/01/2022 up to 08/21/2022. as soon as I remove the field Teams from my table the MAX date goes back to 08/31/2022. I am trying to figure out a way to display the cost of a date range given by the slicer per team without having my min/max dates impacted by my data dates. Is that possible?
Hi,
You should create a Calendar Table with calculated columns for Year, Month name and Month number. Sort the Month name by the Month number. Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table. To your slicer/filter/visual, drag Year and Month name from the Calendar Table. Now your measures in the visual should work fine.
Hi itsmebvk,
What's the result you expect from the Between mode Slicer? The Between mode shows us a large range of dates. Actually, only the dates in the tables works. It's ten days in your demo. In other words, the Between mode doesn't provide any dates.
If you'd like the Max and Min values to change according to the slicer, you need a date table which provides continuous dates.
Best Regards,
v-jiascu-msft Thanks for your reply.
I am unaware that Between mode slicer only supports continuous dates, but surprisingly List kind slicer is giving Max and Min using same dates.
However I am using a workaround for it, I created a calendar table using Calendar Auto function then joined that table with my table on dates and used Calendar Date table for slicer. Its giving expected results.
Hi itsmebvk,
I'm glad you made your project work.
It isn't accurate to say "only supports continuous dates". The Between mode provides a large range of dates. But the actual dates are the dates behind it that are in the column.
For instance, one column only has 2018-01-01 and 2018-12-31. The Between mode will show up a large range from 2018-01-01 to 2018-12-31. How many dates are there? It's only two days. If we change the range to 2018-06-01 to 2018-12-31, there will be only 1 day.
The Between mode slicer gives us options but doesn't change anything. That's how it works.
Best Regards,
