cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chrisdotnisbet1
Helper I
Helper I

Most Recent value - help

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?
 
 
1 ACCEPTED SOLUTION
ibarrau
Super User II
Super User II

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,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

View solution in original post

2 REPLIES 2
ibarrau
Super User II
Super User II

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,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

View solution in original post

This seems to have worked! Thanks so much!

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.