Measure: Return the most recent value that is not blank.

[Kev_Tord1]

Hi everyone,

I was wondering if someone could help me creating a measure for the following scenario.

Purpose: Capturing the most recent "Work ratio" that is not blank. Both "Effective Date" and "Work Ratio" are in the same table "Employment".

I have played with a few formulas but it doesn't seem to return the expected value.

In this case, I would expect the measure to return 0.60

Thanks for any help,

[Ashish_Mathur]

Hi,

Here's my approach.  Create a Calendar Table with a relationship (Many to One and Single) from the Effective date column to the Date column.  In the Calendar Table, write calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number column.  To your visual, drag Year and Month fro the Calendar Table.  Write this measure

Measure = calculate([Work ratio],lastnonblank(Calendar[date],calculate([Work ratio])))

Hope this helps.

[Ahmedx]

pls try