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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

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.