Max date where another value is not blank

[squarecat]

Hello guys,

I'm having trouble creating a measure that shows the max date where the value in another column is not blank

For example here it should show the date 1/3/2025.

I have seen a lot of information on how to do the opposite but basically none on this matter, I would appreciate any help.

[danextian · Accepted solution]

Hi @squarecat 

Your description is unclear. You want to find the maximum date where another column has a non-blank value, yet you expect the result to be January 4, where the value in that column is blank. Try either of these:

Last Nonblank Date = 
CALCULATE (
    LASTNONBLANK ( MyData[Date], CALCULATE ( SUM ( MyData[Value] ) ) ),
    ALL ( MyData )
)

Earliest Blank = 
CALCULATE (
    MIN ( MyData[Date] ),
    FILTER ( ALL ( MyData ), ISBLANK ( MyData[Value] ) )
)

[squarecat]

You are right, apologies. I was looking for the last non blank date meaning 1/3/2025 so the first solution is the one that works for me.

Thank you!

[bhanu_gautam]

@squarecat , Create a measure

MaxDateWithNonBlankValue =
CALCULATE(
MAX('Table'[Date]),
NOT(ISBLANK('Table'[Value]))
)

[squarecat]

Thanks for taking the time to answer, your solution works! however when trying to use it for making an offset date in my calendar table it doesn't seem to recognize it.

Thankfully the solution proposed by the other user seems to work very well, I will attach the calculated column I made so it can help others.

Offset month = 
VAR vlast =
    CALCULATE(
    LASTNONBLANK('Table'[Date], CALCULATE(SUM('Table'[Value]))),
    ALL ('Table'))
VAR vResult =
    DATEDIFF (vlast, 'Table'[Date], MONTH ) + 1
RETURN
    vResult