Forum Discussion

PowerQ12's avatar
PowerQ12
New Member
2 years ago

Help with MIN & MAX Commands

I'm hoping for a bit of guidance as I'm struggling to get a meaninful result.

 

I have my data set out below:

Claim ReferenceInsuredIncurredReporting PeriodMax DateMin Date
CL94887John Smith£17,00031/10/2331/12/2331/10/23
CL94887John Smith£65,00030/11/2331/12/2331/10/23
CL94887John Smith£70,00031/12/2331/12/2331/10/23

 

Just for context, I receive monthly insurance claim declerations which get imported into Power BI automatically. The data doesn't get overwritten if it's the same, it just get's added to the bottom. If no figures (such as the incurred changes) it adds a new row with just the reporting period changed with the same figures.

 

What I'm looking to acheive is looking at the life cycle of claims from the first month it's reported, to the latest month. Taking the example above, It first appeared in October 2023 at £17,000 and in December, it moved to £70,000. Therefore, we have an increase of £53,000. What I want to do is create a measure that looks at this difference as an amount based on the min and max dates and possibly the accuracy of the intial incurred to what is is not as a %.

 

Is this something that's possible?

 

Thanks

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    I tried to create a sample pbix file like below.

     

     

     

    Expected result measure: =
VAR _mindatevalue =
    CALCULATE (
        SUM ( Data[Incurred] ),
        WINDOW (
            1,
            ABS,
            1,
            ABS,
            SUMMARIZE ( Data, Data[Claim Reference], Data[Insured], Data[Reporting Period] ),
            ORDERBY ( Data[Reporting Period] ),
            ,
            PARTITIONBY ( Data[Claim Reference], Data[Insured] )
        )
    )
VAR _maxdatevalue =
    CALCULATE (
        SUM ( Data[Incurred] ),
        WINDOW (
            -1,
            ABS,
            -1,
            ABS,
            SUMMARIZE ( Data, Data[Claim Reference], Data[Insured], Data[Reporting Period] ),
            ORDERBY ( Data[Reporting Period] ),
            ,
            PARTITIONBY ( Data[Claim Reference], Data[Insured] )
        )
    )
RETURN
    _maxdatevalue - _mindatevalue
    • PowerQ12's avatar
      PowerQ12
      New Member
      2 years ago

      This is great, thank you so much for this. This is exactly what I was looking for.

       

      Is there a way to use this Measure to work out a % accuracy value and aggregate this as an overall figure per reporting period?

       

      thanks again!

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        2 years ago

        Hi,

        Please provide your sample pbix file with describing how the expected outcome looks like.

        And then I can try to look into it.

        Thank you.