Most recent date note

Happy New Year!

Please help me with the DAX formula for the “most recent date note”.

I have multiple tables ‘ GP_AR_TB_Historical_Aging
AR_Aging-2022-01-04.pbix (1.9 MB)
&’ GP_ AR Aging with customers’ open balances.

Sometime balance is recorded by Customer Invoices number [Document Date] sometime by Customer account Number [Customer Number]

I have another table ‘Notes, where I collect all records with notes by [NoteDate] and [Document Date] and [Customer Number].

How can I link the most recent [NoteDate] from ‘Notes to tables ‘GP_AR_TB_Historical_Aging & **’ GP_ AR Aging?

Thank you very much!

Natasha

Hi,

Can you tell me what we are trying to achieve here and on which column name will be used for connecting the tables and on which bases we can find the latest date .

It will help me and others folks to help you better.

Thanks,
Anurag

Thank you very much, Anugar for your help!
I need to have it in a couple of places:)

Tab: Hist TB Aging by date
Table: Past Due TOP 20 ( LAST NOTE ON THE ACCOUNT in a seporate column)
Table: AR AGING ( LAST NOTE ON THE ACCOUNT in a seporate column)

Tab: Jennifer’s Dashboard
Table: PAST DUE AR ( LAST NOTE ON THE ACCOUNT in a seporate column)
Table: WORKING-FILE_ ( LAST NOTE ON INVOICE LEVEL in a seporate column)

Did I answer your question?
Thank you!

Thanks for your reply your answerer my where part of my question can you tell me on which bases date should be calculated a mockup will be grt to understand because when i try to find thing on the base of customer id is show me blank only

Sorry, Anugar…I don’t understand " mockup"
I have the LAST NOTE DATE question :slight_smile:

I might confused with mixing up “tables” and “visuals”, sorry about it!
Both Tables: “GP_AR_TB_Historical_Agin” & “GP_ AR Aging” has column [Customer Number] 6 digits number

Notes dates are located in a table “Collection Files with Note”, this table has the same column [Customer Number] .

For Example ( assuming we are running report today for 4 invoices):
INV # 10945 ClientABC NOTE DATE 1/1/2022 ← This note will be display on the account level and invoice level
INV # 10987 ClientABC NOTE DATE 1/1/2022 ← This note will be display on the invoice level only
INV # 10223 ClientABC NOTE DATE 11/11/2021 ← This note will be display on the invoice level only

INV # 57 ClientDFG NOTE DATE 3/21/2021 ← This note will be display on the account level and invoice level

Visuals
Tab: Hist TB Aging by date

Visual: Past Due TOP 20 ( LAST NOTE ON THE ACCOUNT in a seporate column)
Visual: AR AGING ( LAST NOTE ON THE ACCOUNT in a seporate column)

Tab: Jennifer’s Dashboard
Visual: PAST DUE AR ( LAST NOTE ON THE ACCOUNT in a seporate column)
Visual: WORKING-FILE_ ( LAST NOTE ON INVOICE LEVEL in a seporate column)

Did I answer your question this time:)?
Thank you!

I do not find the the column you are saying here but there is column number in the table and can i have and expected result for your req like for the below visual what should come in the required column:
image
This is what i mean by mockup of expected result.

Hi Anurag-
Please see updated new PB File.
AR_Aging-2022-01-06.pbix (3.4 MB)

I displaied CstomerID (sory again not CustomerNum) everywhere
Third time is a charm! Thank you!.

Morning Anugar -
Do you need more information? or it’s good now? Thank you!

Bumping this post for more visibility.

Hi @npower

Have gone through the post and similar to Anurag, not able to understand the requirement.

It will be good if you can share the expected result using a live example from the sample data.

Thanks
Ankit J

Hello @npower, it’s been a while since we got a response from you.

Just following up the information the other users requested above so they can help you further. Please take note that inability to provide all the information may result to delay in getting answers.

Also, In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi Ankit-

Thank you for your help!

I don’t know how to post screenshots on this forum, but I simplified PBi File ( please see attachment).

Table1

Column[Number] is linked to notes: dates, notes and Document Date (in red)

Table 2

Column [Last Note] should display the last note on the Customer Account (Table 1, Column[Number])

Table 3

Column [Last Note] should display the last note on the Customer Account (Table 1, Column[Number])

Table 4

Column [Last Note] should display the last note on the Document Number t (Table 1, Column[Number] & Column [Document Number])

Please let me know if you need more clarifications!

Thank you,

Natasha

AR_Aging-2022-01-06.pbix (3.3 MB)

Hi,

Still not clear you can attach an expected result with help of this bottons in reply section:
image

In above explanation pls use the table name you used in your model the name of table are different from the file

Thanks,
Anurag

I did, did you open my Power BI file?

Yes,

The table name is not matching and pls include expected result in the file also :

Thanks,
Anurag

VISIUAL -->TOP LEFT CORNER
Table: Collection with Notes
It’s referring to the table name, which you colored in yellow and columns to look into…
Coluimns are highlighted in red/pink
Sorry, but I cannot understand what else do you need?
Thank you!

Hi there,

To be honest it was confusing to me too what you trying to accomplish.

I think you are using the wrong terms that is related. Tables are your data tables that under the field tab. Your visualization that you stated “tables” aren’t classification as tables. To me they are called visualization displays.

I think that is where the confusion is occurring. Maybe change the titles on your visualization to Using Data Table ______

What @Anurag is trying to ask is. What do you want to see as a final result? You need to provide that information so @Anurag can make sure the ending result that what you need.

thanks
Keith

Sorry I don' see inser  the picture tool , but'im uploading it now :)

Hi,

The left corner visual contain note and last date we need to get those value based on customerID number in other visual .

Is it right understanding.

Thanks,
Anurag

Correct!!!