locked
Last claim filed by customer RRS feed

  • Question

  • Hello. I have a customer table (one), a claims table (many) and a date table. I want to see the last date of claim filed for each customer. How can I do this?
    Friday, August 19, 2016 10:17 PM

Answers

  • Hi Alex,

    >> For the formula below, how is ALLEXCEPT working? Is this saying don't filter on anything except for customer ID?
    Yes, ALLEXCEPT function means that removes all context filters in the table except filters that have been applied to the specified columns(customer ID).

    >>So if it sees a customer ID, it looks at all the same customer IDs?
    Yes, it will all the same customer IDs. CALCULATE function evaluates an expression(MAX(Claim[ClaimDate])) in a table that is modified by the specified filters(ALLEXCEPT).
    In the follow formula, it will calculate the max date in same customer IDs. As a result, every customer will match a max date as below figure.
    =CALCULATE(MAX(Claim[ClaimDate]),ALLEXCEPT(Claim,Claim[CustomerID]))
     
    >> I'm just learning about lookupvalue but why can't I just use related?

    From what I know, it’s unreasonable to use Max in Lookupvalue function. It will returns the claimdate of customer rather than the max date if you just use Lookupvalue function.

    >> Lastly, how do I connect these maxdates to the date table?
    Please provide us more information about your data model structure, if possible provide us some sample data and screenshots, so that we can make further analysis.

    Regards,


    Charlie Liao
    TechNet Community Support


    • Edited by Charlie Liao Wednesday, August 31, 2016 10:37 AM
    • Proposed as answer by Charlie Liao Sunday, September 4, 2016 9:20 AM
    • Marked as answer by Charlie Liao Thursday, September 8, 2016 9:38 AM
    Wednesday, August 31, 2016 10:36 AM

All replies

  • Hi Alex,

    Create a calculated column to get the max date in claims table by using Calculated function, and then get this data in customer table by using Lookupvalue function.
    =CALCULATE(MAX(Claim[ClaimDate]),ALLEXCEPT(Claim,Claim[CustomerID]))
    =LOOKUPVALUE(Claim[MaxDate],Claim[CustomerID],Customer[ID])


    Regards,


    Charlie Liao
    TechNet Community Support

    Sunday, August 21, 2016 9:25 AM
  • Hi Charlie. Thank you. 

    A couple of follow up questions. For the formula below, how is allexcept working? Is this saying don't filter on anything except for customer ID? So if it sees a customer ID, it looks at all the same customer IDs?

    =CALCULATE(MAX(Claim[ClaimDate]),ALLEXCEPT(Claim,Claim[CustomerID]))

    I'm just learning about lookupvalue but why can't I just use related? 

    Lastly, how do I connect these maxdates to the date table? I want to calculate customer renewal rates based on the last date that I just calculated. The customer table is connected to the date table and there is a renewal table connect to the customer table. Not sure if I need to add something to get renewal rates of customers BASED on this max date. Thanks!!


    Monday, August 22, 2016 5:40 PM
  • Hello. I created a maxdate for a customer table that shows the last date that they filed a claim. The customer table is linked to the purchase and renewal table. I want to see renewal rates based on last claim date (1 month from start date, 2 months from start date, etc). How can I do this? 
    • Merged by Olaf HelperMVP Wednesday, August 24, 2016 6:00 PM Similar question from same OP
    Monday, August 22, 2016 8:47 PM
  • Hi Alex,

    >> For the formula below, how is ALLEXCEPT working? Is this saying don't filter on anything except for customer ID?
    Yes, ALLEXCEPT function means that removes all context filters in the table except filters that have been applied to the specified columns(customer ID).

    >>So if it sees a customer ID, it looks at all the same customer IDs?
    Yes, it will all the same customer IDs. CALCULATE function evaluates an expression(MAX(Claim[ClaimDate])) in a table that is modified by the specified filters(ALLEXCEPT).
    In the follow formula, it will calculate the max date in same customer IDs. As a result, every customer will match a max date as below figure.
    =CALCULATE(MAX(Claim[ClaimDate]),ALLEXCEPT(Claim,Claim[CustomerID]))
     
    >> I'm just learning about lookupvalue but why can't I just use related?

    From what I know, it’s unreasonable to use Max in Lookupvalue function. It will returns the claimdate of customer rather than the max date if you just use Lookupvalue function.

    >> Lastly, how do I connect these maxdates to the date table?
    Please provide us more information about your data model structure, if possible provide us some sample data and screenshots, so that we can make further analysis.

    Regards,


    Charlie Liao
    TechNet Community Support


    • Edited by Charlie Liao Wednesday, August 31, 2016 10:37 AM
    • Proposed as answer by Charlie Liao Sunday, September 4, 2016 9:20 AM
    • Marked as answer by Charlie Liao Thursday, September 8, 2016 9:38 AM
    Wednesday, August 31, 2016 10:36 AM
  • Thanks Charlie. 
    Monday, September 12, 2016 6:04 PM