Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
1 year ago
Solved

Latest Value Measure not Working

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

 

 

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

  • Akash_Varuna's avatar
    Akash_Varuna
    Super User
    1 year ago

    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