topic Re: Help with MIN & MAX Commands in DAX Commands and Tips

Help with MIN & MAX Commands

PowerQ12 — Wed, 07 Feb 2024 16:19:33 GMT

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 Reference	Insured	Incurred	Reporting Period	Max Date	Min Date
CL94887	John Smith	£17,000	31/10/23	31/12/23	31/10/23
CL94887	John Smith	£65,000	30/11/23	31/12/23	31/10/23
CL94887	John Smith	£70,000	31/12/23	31/12/23	31/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

Re: Help with MIN & MAX Commands

Jihwan_Kim — Wed, 07 Feb 2024 17:58:04 GMT

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

Re: Help with MIN & MAX Commands

PowerQ12 — Thu, 08 Feb 2024 14:47:20 GMT

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!

Re: Help with MIN & MAX Commands

Jihwan_Kim — Thu, 08 Feb 2024 16:03:30 GMT

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.