How to get the MIN and MAX Dates of a "Between" Date Slicer

[Sean]

I'm trying to get the Date Range - Min and Max Dates of a Between Date slicer.

Specifically the MIN Date which I need to calculate the average only for the selected period.

However when I click on any column in the chart I can't get the correct MIN Date!

Here's a sample file

Any help/ideas will be greatly apprecaited!

Thanks!

@OwenAuger@Phil_Seamark@KHorseman

That date will actually be used on a Tooltip Page and the chart you see on the right is actually on the "visible" page.

But what happens when you hoover over the columns of the chart each column in effect filters the results of the tooltip page just like if you've clicked on the column in the sample file! This is fine for everything I display on the tooltip page except the average I'm trying to calculate which calculates only for the specific column/date or for the entire calendar. Hope this makes sense!

[OwenAuger · Accepted solution]

Hi @Sean

In this case, I think the issue is that when you click on a bar of the column chart (or hover in the case of a tooltip), any other visuals that are filtered by this visual are filtered just the same as if a slicer had been filtered on the Date corresponding to that bar. In the words of this article, the last shadow filter context of 'Calendar Table'[Date] becomes the same as the Date filter context itself, so ALLSELECTED has no way of retrieving the Date filter from the slicer.

To achieve your intended result, I believe you need two separate Date columns: one for the slicer and one for the chart.

You could either use the Date from the fact table as your second date, or construct another Date table related to the first. 

I've gone with constructing another Date table, sort of a similar approach to Alberto Ferrari in this article.

1. Create a 'Calendar Filter' table identical to Calendar, and put it on the 1-side of a 1:many relationship with 'Calendar Table'.
2. Use 'Calendar Table Filter'[Date] on the slicer
3. Use 'Calendar Table'[Date] on the axis of the visual
4. Rewrite MIN Date Range ALLSELECTED as:
   

   Min Date Range ALLSELECTED =
   CALCULATE (
       MIN ( 'Calendar Table Filter'[Date] ),
       ALLSELECTED ( 'Calendar Table Filter')
   )

   (actually Min Date Range ALL as already defined also works with these model changes)
5. Sample file uploaded here:
   PBIX link

Also related idea in this article
http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-powe...

Regards,

Owen

[Sean]

@OwenAuger

I ended up using the Calendar Filter table but because each Product had a different starting date I had to add a simple IF statement to decide which date to use when calculating the average for the selected product.

Even though we got the correct Min Slicer Date when a column was clicked I had to add this simple condition...

If the Min Slicer date selected was prior to the Min Overall Date for the selected product use the Min Overall Date

otherwise go with the Min Slicer Date to calculate the average.

Thanks again!