MIN and FIRSTNONBLANK functions returns wrong value from a slicer

[mtomova]

Hi!

I have a dynamic title card, in which I want to show the range selected in a slicer.

The slicer holds the Month Year values for few years period.

I am using the following DAX to get the selected range:

Op Forecast - Cap Spend Months Rng = 

"Capital Spend Forecast for " & 

FIRSTNONBLANK('Calendar Calcs'[Year Month], MIN('Calendar Calcs'[Year Month])) & " - " & LASTNONBLANK('Calendar Calcs'[Year Month], MAX('Calendar Calcs'[Year Month]))

In the slicer I have selected Jan 2023 + Feb 2023 + March 2023, so what I exptect to see is:

"Capital Spend Forecast for Jan 2023 - March 2023", but instead I get Feb 2023 - March 2023.

I am pretty sure that this happens, because the Year Month column is formatted as Text, 

but I have also tried the following code, to get first and last non blank values from the selection in the slicer

Op Forecast - Cap Spend Months Rng =
"Capital Spend Forecast for " &
FIRSTNONBLANK('Calendar Calcs'[Year Month], 1) & " - " & LASTNONBLANK('Calendar Calcs'[Year Month], 1)

and the result is again Feb 2023 - March 2023, instead of Jan 2023 - March 2023

Any advice how to approach this challenge will be highly appreciated!

[jdbuchanan71 · Accepted solution]

You are correct about the text column so it is getting sorted alphabetically.  We need to use a numeric column to get the lowest and highest like this.

Op Forecast - Cap Spend Months Rng = 

"Capital Spend Forecast for " & 
VAR _Min = MIN('Calendar Calcs'[Date])
VAR _Max = MAX('Calendar Calcs'[Date])
RETURN
CALCULATE(SELECTEDVALUE('Calendar Calcs'[Year Month]),'Calendar Calcs'[Date]=_Min) & " - " & CALCULATE(SELECTEDVALUE('Calendar Calcs'[Year Month]),'Calendar Calcs'[Date]=_Max)

[mtomova]

Wow, that is exactly what I was after!

Thank you for your help!