cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
setis Member
Member

Latest value from another table

Dear all,

 

There are several topics about finding the latest value but I can't get it working for me.

 

I have a [Cases] table with the columns "CaseID" and "CreatedOn" (Date&time)

 

and a second table [Finance] with the columns "CaseID" and "Posting Date".

 

I have an innactive relationship between Cases[CaseID] 1to* Finance[CaseID]

 

I am trying to calculate the DATEDIFF between [Cases]CreatedOn and the latest Finance[Posting Date]

 

I hope that the question makes sense.

 

Could somebody please help me?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Latest value from another table

Hi @setis

Try this:

 

1. Create these three measures:

 

ResolutionDate = MIN(CaseResolutionL[CreatedDate])
PaymentDate = MAX(Payments[Posting Date])
 
ReimbursementDate =
CALCULATE (
    VALUES ( Reimbursements[Posting Date] );
    Reimbursements[Remaining Amount] = 0
)

2. Then you can create other measures combining the ones above. For instance, according to your description above: 

Days1to2 = SELECTEDVALUE(CasesL[CreatedOn]) - [PaymentDate]

Days2to3 = [PaymentDate] - [ResolutionDate]

Days3to4 = [ReimbursementDate] - [PaymentDate]

 

3. Place CasesL[CaseId] in the rows of a matrix visual and the measures above in values of the matrix

 

I think it would be good to review the tables for consistency as right now you seem to have, for example, Ids in the Resolutions table that do not appear in CasesL

 

 

 

 

View solution in original post

6 REPLIES 6
Super User III
Super User III

Re: Latest value from another table

Hi @setis

It would probably help if you showed your tables as well, in addition to describing them.

Let's see if I've understood correctly.  Try this:

1.  Make that relationship active

2. Set Cases[CaseID] in the rows of a matrix visual

3. Set this measure in values of the matrix visual:

 

 

DiffLastPost_Creation =
MAX ( Finance[Posting Date] ) - SELECTEDVALUE ( Cases[Created] )

 

This will show the difference in days, where the decimal part will indicate a fraction of a day. You could convert that to hours, mins, secs if so required

 

 

setis Member
Member

Re: Latest value from another table

Dear @AlB

 

Thanks a lot for the effort. It's not working for me. 

 

I'm sorry if I wasn't clear enough

 

I have 3 main tables: "Cases", "CaseResolution" and "Financial_combined"

 

I am trying to do with it calculated columns. I would prefer to do it with meassures if possible. 

 

In order to track the duration of a case I need to calculate Financial_combined[Posting Date] - CaseResolution[CreatedOn] - Cases[CreatedOn]

 

The screenshot is from a table of activities. 

I am bringing in a calculated column the Case Created date with: 

CCaseCreatedOn = LOOKUPVALUE(Cases_ACT[CreatedOn];Cases_ACT[CaseID];CaseResolution[CaseID])
 
To get the posting date from the Financial table, I tried with another Lookupvalue but it's not doing it for me:
 Capture.PNG
 
 
mm
Super User III
Super User III

Re: Latest value from another table

@setis

 

I can't really help if you don't show the structure of your tables and detail their relationships. It's better if you show the tables in text-tabular format in addition to (or instead of) the screen captures. Just use copy table in Power BI and paste it here. Then the data can be readily copied. Or even better if you can share the pbix (beware of confidential info).

 

I'm still not clear on what you want to do. Maybe you can explain it a bit more, ideally with an illustrative example based on your sample data? I don't quite understand this:

In order to track the duration of a case I need to calculate Financial_combined[Posting Date] - CaseResolution[CreatedOn] - Cases[CreatedOn] 

 

What is each of those three fields?

Highlighted
setis Member
Member

Re: Latest value from another table

Dear @AlB,

 

I appreciate how difficult it must be trying to help someone without the proper data.

 

I prepared a stripped down version of my database.

 

The case flow is the following: 

 

1.- A case is created [CasesL]

2.- The case is resolved [CaseResolutionL] (there can be more than one resolution. I'm interested in the first one.

3.- The case is paid [Payments] (there can be more than one per case. I'm interested in the last one. 

4.- The case is reimbursed (there can be more than one. I'm interested in the one where the remaining amount is 0

 

What I'm trying to record is the time (days) from 1 to 2, 2 to 3 and 3 to 4. 

 

PS. In the file I'm using the delimiter is ";" instead of ","

 

 

 

Thank you so much for your help in advance. I've tried 

Super User III
Super User III

Re: Latest value from another table

Hi @setis

Try this:

 

1. Create these three measures:

 

ResolutionDate = MIN(CaseResolutionL[CreatedDate])
PaymentDate = MAX(Payments[Posting Date])
 
ReimbursementDate =
CALCULATE (
    VALUES ( Reimbursements[Posting Date] );
    Reimbursements[Remaining Amount] = 0
)

2. Then you can create other measures combining the ones above. For instance, according to your description above: 

Days1to2 = SELECTEDVALUE(CasesL[CreatedOn]) - [PaymentDate]

Days2to3 = [PaymentDate] - [ResolutionDate]

Days3to4 = [ReimbursementDate] - [PaymentDate]

 

3. Place CasesL[CaseId] in the rows of a matrix visual and the measures above in values of the matrix

 

I think it would be good to review the tables for consistency as right now you seem to have, for example, Ids in the Resolutions table that do not appear in CasesL

 

 

 

 

View solution in original post

setis Member
Member

Re: Latest value from another table

Thank you so much for this @AlB!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors