Trying to calculate YTD Severity

[senencito]

Hi there,

I'm trying to calculateyearly severity starting from January. In thisexcel screenshot I have two examples that show what i'm getting vs what i'm supposed to get.

As you can see on the screenshot, I should be adding the number of paid claims from the previous months and dividing it by the amount of money paid over the same amount of months.

When I bring it to powerbi, having the month on the column makes it so the information is alwys filtered for the current month:

I already have it to filter just for the current year, but i'm not sure how to do the cumulative monthly comparison.

-- This is the code i'm using. runningTotal here refersto the cumulative YTD

runningTotal =

IF (YEAR(TODAY()) = YEAR(MAX(scs_claims_paid_totals[ClaimPaidDate])) ,

CALCULATE(

SUM(scs_claims_paid_totals[TotalPaid]) / DISTINCTCOUNT(scs_claims_paid_totals[ClaimNumber])

, YEAR(TODAY()) = YEAR(scs_claims_paid_totals[ClaimPaidDate]) , MONTH(date_dimension[Date]) <=(date_dimension[Month]) )

, 0)

would really appreciate any help that I can get with this.

[Ehren]

Hi senencito. This appears to be a DAX question, not a Power Query one. You might get more traction if you move this to the area of the forum for DAX questions.

[v-eqin-msft]

Hi @senencito ,

Since there are Measures not the original data according to the scrrenshots, please provide a data sample or share me with your pbix file after removing sensitive data.

Best Regards,
Eyelyn Qin

[senencito]

Hey all, apologies for the delay. I've uploaded a pbi file without sensitive information.

Also in the folder there's an Excel file i'm using as my data set. Would love all the help I can get.

https://www.dropbox.com/sh/uwitk60yfdnqc4o/AACDDIHUmihbzEiiOg1XqXsAa?dl=0