Pulling most update value and excluding blanks

[Daniel48]

Latest Value = 
VAR _currentCategory = Data[Category]
VAR _maxDate =
    CALCULATE (
        MAX ( Data[Date] ),
        FILTER ( Data, Data[Category] = _currentCategory )
    )
VAR _leastVal =
    CALCULATE (
        MAX ( Data[Current] ),
        FILTER ( Data, Data[Date] = _maxDate && Data[Category] = _currentCategory )
    )
RETURN
    _leastVal

Is it possible to have this look only non blanks. For example the newest item has a blank value but the year prior has a value. I would like to pull the prior if there is no current 

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result very clearly.

[quantumudit · Accepted solution]

Hey @Daniel48 

Here is the DAX formula to achieve the desired result; it will retrieve the current value for the most recent year with a non-blank value.

Latest Value = 
VAR _currentCategory = Data[Category]
VAR _maxDate =CALCULATE(
        MAX(Data[Date]) ,
        FILTER ( Data, Data[Category] = _currentCategory && Data[Current] <> BLANK())
    )
VAR _leastVal =
    CALCULATE (
        MAX ( Data[Current] ),
        FILTER ( Data, Data[Date] = _maxDate && Data[Category] = _currentCategory )
    )
RETURN
    _leastVal

With just a minor tweak, I need to add an AND condition.

Below is a snapshot with the highlighted change in the formula and the resulting dataset. I tested the formula by setting the X01 latest year's current data to blank, and it worked.

Give it a try and let me know.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

[Daniel48]

Thank you that worked!