Latest value from another table

[setis]

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?

[AlB]

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]

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:

 

mm

[AlB]

@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?

[setis]

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 

[AlB · Accepted solution]

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

[setis]

Thank you so much for this @AlB!