Latest Value Measure not Working

[ArchStanton]

Hi

I have the following table located in an Excel Tab called 18mth+ Closed

The latest value my measure is showing is 1109 which is 3 months ago!

Why isn't the measure showing 1384 which is the Feb figure?

LatestValue18mths+ Closed = 
    VAR _lastdate = 
            CALCULATE(MAX('18mth+ Closed'[Date]),
            FILTER('18mth+ Closed','18mth+ Closed'[KPI] <> BLANK() )
            )
    RETURN
        CALCULATE(
                MAX('18mth+ Closed'[KPI]),
                FILTER ('18mth+ Closed', '18mth+ Closed'[Date] = _lastdate)
        )

Thanks

[v-cgao-msft · Accepted solution]

Hi @ArchStanton ,

1. Desktop --> home pane --> transform data --> transform data

2. Powerquery editor --> add column --> index column --> close and apply.

Modify the measure:

LatestValue18mths+ Closed = 
    VAR _lastindex = 
            CALCULATE(MAX('18mth+ Closed'[Index]),
            FILTER('18mth+ Closed','18mth+ Closed'[KPI] <> BLANK() )
            )
    RETURN
        CALCULATE(
                MAX('18mth+ Closed'[KPI]),
                FILTER ('18mth+ Closed', '18mth+ Closed'[Index] = _lastindex)
        )

and the output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[ArchStanton]

Thank you, this works perfectly

[Akash_Varuna]

Hi @ArchStanton Try this 

LatestValue18mths+ Closed =
VAR _lastdate =
    CALCULATE(
        MAX('18mth+ Closed'[Date]),
        FILTER(
            '18mth+ Closed',
            NOT(ISBLANK('18mth+ Closed'[KPI]))
        )
    )
RETURN
    CALCULATE(
        MAX('18mth+ Closed'[KPI]),
        '18mth+ Closed'[Date] = _lastdate
    )

If this popst helped please do give a kudos and accept this as a solution
Thanks In Advance