Forum Discussion

mtomova's avatar
mtomova
Icon for Helper III rankHelper III
3 years ago
Solved

MIN and FIRSTNONBLANK functions returns wrong value from a slicer

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!

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    3 years ago

    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)

     

     

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      mtomova
      Icon for Helper III rankHelper III
      3 years ago

      Wow, that is exactly what I was after!

      Thank you for your help!