Forum Discussion

senencito's avatar
senencito
Frequent Visitor
4 years ago

Trying to calculate YTD Severity

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.

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

  • Ehren's avatar
    Ehren
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    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.