DAX to get MIN/MAX date from a slicer but ignoring date in matrix

[dancarr22]

Hello,

The slicer and the matrix in the screenshot below both reference the same date table.  The Date table has every day between 1/1/1995 to 12/31/2024.

How can I get the MIN and MAX date from the AsOfDate slicer and reference in the matrix?  If I just calc min/max date it only displays MIN/MAX for the current year - as is grouped in the matrix.  If I use ALL it removes all filters and doesn't even use the slicer filter dates.  So, I'd want the calc to display (MIN) 5/13/2006 in every cell in the matrix - and MAX of 3/20/2019.

This is needed for financial calcs we are performing.

Appreciate any assistance you can provide.

Thanks,

Dan

[OwenAuger · Accepted solution]

Hello @dancarr22 

ALLSELECTED is your friend here 🙂

The expression

CALCULATE (
    MIN ( 'Date'[Date] ),
    ALLSELECTED ()
    -- or ALLSELECTED ( 'Date' )
)

evaluated at the "top level" of a measure used directly in the visual will return the minimum date ignoring filters due to grouping within the visual (roughly speaking), which in your case should be the minimum filtered on the slicer.

Similarly for max date.

See this article for a full discussion of ALLSELECTED:

https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

Does this work for you?

Regards,

[dancarr22]

Thanks @OwenAuger !!!  That worked