topic Re: DAX to get MIN/MAX date from a slicer but ignoring date in matrix in DAX Commands and Tips

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

dancarr22 — Sat, 08 Jul 2023 04:08:53 GMT

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

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

OwenAuger — Sat, 08 Jul 2023 10:39:28 GMT

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,

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

dancarr22 — Thu, 13 Jul 2023 18:54:06 GMT

Thanks OwenAuger !!! That worked