Forum Discussion
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
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
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