Between slicer issue while capturing Min and Max values on Date column (is it known issue??)

[itsmebvk]

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.

[v-jiascu-msft · Accepted solution]

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,

[v-jiascu-msft]

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,

[itsmebvk]

@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.

[v-jiascu-msft]

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,

[itsmebvk]

@v-jiascu-msft in the given screen shot I have dates in my column from 1/10/2018 to 1/14/2019. Assume this as order date. When we create a calculated columns for Max order date and Min order date based on Between slicer it should display based on slcier values. For example if we select 01/10/2018 to 01/14/2018 in Between slicer still it is showing 01/10/2018 as Min date and 1/14/2019 as max date. But if we change slicer type to list its showing min date as 01/10/2018 and max date as 01/14/2018. 

[v-jiascu-msft · Accepted solution]

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,

[itsmebvk]

@v-jiascu-msft I am extremely sorry for the confusion. Since I have similar dates as highlighted i got confused. Please disregard my previous comments saying that i am using calculate columns, actually I am using calculated measure. And its working fine. . Please see the attached PBIX.

[v-jiascu-msft · Accepted solution]

That's OK. I'm glad you found the root cause and solved this issue.

Best Regards,

[itsmebvk]

Hi Folks, anyone faced this issue??

[ERIC_A]

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? 

[Ashish_Mathur]

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.

[ERIC_A]

I do have a calendar table with day month year granularity up to 2024 and it is linked with my data table by creation date and closed date.  the only difference is that the calendar table is not calculated, It is just static. Would that make any difference? 

[Ashish_Mathur]

Share some data and show the expected result.

[ERIC_A]

HI I cannot share data due to security policies but this is what I can share 

Date field coming from my generic Calendar table 

Expected result is to see Min Date = 8/30/2022 and Max Date 9/29/2022. Instead my table shows this 

This is my max and min formula

MaxDate = CALCULATE(Max('Calendar'[Date]),ALLSELECTED('Calendar'))

Mindate = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'))

My table relationship between my data table which has (group by dates for ticket creation and archived for ticket closed date).

[Ashish_Mathur]

Hi,

Change the relationship to Single (instead of bi-directional).  Also, you can simplify your measures to:

=Max('Calendar'[Date])

=Min('Calendar'[Date])

Hope this helps.

[ERIC_A]

Hi I found the answer! I wrapped my formula in a 2nd calculate and then added a crossfilter function to remove the filter and it worked! 

Mindate = calculate(CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar')),CROSSFILTER('Calendar'[Date],Table1[Group By Dates],None))

MaxDate = calculate(CALCULATE(Max('Calendar'[Date]),ALLSELECTED('Calendar')),CROSSFILTER('Calendar'[Date],Table1[Group By Dates],none))

Now my min date is 08/01/2022 and my max date is 08/31/2022 for all teams I have available. 

thank you all!