Forum Discussion

setis's avatar
setis
Post Partisan
7 years ago
Solved

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 sec...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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

     

     

     

     

AlB's avatar
AlB
Community Champion
7 years ago

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's avatar
setis
Post Partisan
7 years ago

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's avatar
    AlB
    Community Champion
    7 years ago

    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's avatar
      setis
      Post Partisan
      7 years ago

      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's avatar
        AlB
        Community Champion
        7 years ago

        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