Most Recent value - help

[Anonymous]

Hi all,

I have a sheet containing collated invoice requests.

Job number     Invoice Date     Invoice Value     Comments

1001                01/01/2020       £500                  ABC

1002                01/02/2020       £750                  DEF

1001                01/03/2020       £100                  GHI

1003                01/03/2020       £500                  JKL

1002                01/04/2020       £250                  MNO

1004                01/05/2020       £1000                PQR

Ultimately I need to get the following:

Most Recent Invoice Date

Value of Most Recent Invoice

Comments of Most Recent Invoice

I have the following Measures working:

MaxInvoiceDate = MAX(

Collated[Invoice Date]

)

MaxInvoiceComments =

LOOKUPVALUE (

Collated[Comments],

Collated[Invoice Date],

    [MaxInvoiceDate],

Collated[Job Number],

    MAX(Collated[Job Number]),

Collated[Comments],

    MAX ( Collated[Comments] )

)

What I'm having issues with, is determining the value of the most recent invoice.

Can anyone help?

[ibarrau · Accepted solution]

Hi. You have to pick an aggregation because there will bemore than one date. First you need a calendar table related with that table. That way you can manage dates easily. You can build a measure like this

Sum of Last date = 
CALCULATE (
    SUM ( 'Table'[Invoice] ),
    LASTDATE ( 'Calendar'[Date] )
)

Hope that helps,

[Anonymous]

This seems to have worked! Thanks so much!