Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Vega_01
Regular Visitor

Calculare 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.

Vega_01_0-1634742954351.png

 

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:

 

Vega_01_1-1634742954352.png

 



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)

 

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

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

Please refer to the sample:

Vlianlmsft_0-1635147349416.png

 

View solution in original post

2 REPLIES 2
V-lianl-msft
Community Support
Community Support

Please refer to the sample:

Vlianlmsft_0-1635147349416.png

 

amitchandak
Super User
Super User

@Vega_01 , based on what I got YTD and MTD with help from date table should help

 

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

 

You can create measure using time intelligence and then divide those

 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

 

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.