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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Calculate Monthly Earned Premium and Sum by YTD

Hi

I'm new to Power BI and stuck with this

Can anyone help?

My Querry for Monthly Earned Premium is like this

Note my month format as 201901,201902 so prior yearmonth is current month-100

 

Accident Earned Premium =
VAR PriorYearMonth = IF(HASONEVALUE('AutoCare Policy Data'[Report Month]),FIRSTNONBLANK('AutoCare Policy Data'[Report Month],'AutoCare Policy Data'[Report Month]),BLANK())-100
Var CurrentMonth=IF(HASONEVALUE('AutoCare Policy Data'[Report Month]),FIRSTNONBLANK('AutoCare Policy Data'[Report Month],'AutoCare Policy Data'[Report Month]),BLANK())
Var CurrentMonthSale = SUMX(FILTER(ALL('AutoCare Policy Data'),'AutoCare Policy Data'[Report Month]=CurrentMonth),'AutoCare Policy Data'[Total Premium Before Tax])
Var PriorYearMonthSale=SUMX(FILTER(ALL('AutoCare Policy Data'),'AutoCare Policy Data'[Report Month]=PriorYearMonth),'AutoCare Policy Data'[Total Premium Before Tax])
Var Rolling11MonthSale= SUMX(FILTER(ALL('AutoCare Policy Data'),'AutoCare Policy Data'[Report Month]>PriorYearMonth&&'AutoCare Policy Data'[Report Month]<CurrentMonth),'AutoCare Policy Data'[Total Premium Before Tax])
RETURN
(CurrentMonthSale+PriorYearMonthSale)/24+Rolling11MonthSale/12
 
Current Maxtrix table in Power BI is like:
Report YearReport MonthTotal Premium Before TaxAccident Earned Premium
20142014111,993,5571,837,995
20142014122,211,3882,013,201
2014 25,264,105 
20152015012,764,6062,148,709
20152015022,072,8204,087,748
20152015031,816,9454,160,573
20152015042,553,0564,204,405
20152015052,017,3134,175,194
20152015062,265,5624,233,950
20152015072,244,2694,201,482
20152015081,985,5544,177,781
20152015092,094,8424,171,563
20152015102,178,2004,126,148
20152015112,045,6194,109,171
20152015122,346,5954,128,022
2015 26,385,380 

 

What I need

Report YearReport MonthTotal Premium Before TaxAccident Earned PremiumYTD Accident Earned Premium
20142014011,723,79171,82571,825
20142014021,575,715209,304209,304
20142014031,949,810356,201356,201
20142014041,879,966515,775515,775
20142014051,919,255674,076674,076
20142014062,024,968838,418838,418
20142014072,767,5931,038,1081,038,108
20142014082,073,6451,239,827 
20142014092,673,4101,437,621 
20142014102,471,0061,651,971 
20142014111,993,5571,837,995 
20142014122,211,3882,013,201 
2014 25,264,10511,884,3223,703,707
20152015012,764,6062,148,7092,148,709
20152015022,072,8204,087,7484,087,748
20152015031,816,9454,160,5734,160,573
20152015042,553,0564,204,4054,204,405
20152015052,017,3134,175,1944,175,194
20152015062,265,5624,233,9504,233,950
20152015072,244,2694,201,4824,201,482
20152015081,985,5544,177,781 
20152015092,094,8424,171,563 
20152015102,178,2004,126,148 
20152015112,045,6194,109,171 
20152015122,346,5954,128,022 
2015 26,385,38047,924,74527,212,060

 

1 ACCEPTED SOLUTION
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous ,

It seems that you want to calculate the Accident Earned Premium and YTD Accident Earned Premium.

From the information, I have a little confused about your original table, do you have the date value for 201401, 201402...201410?

In addition, what it your logic to calculate for the Accident Earned Premium?

Besides, from the table what you need, I have a little confused about the result for YTD Accident Earned Premium.

From my understand for YTD, the output should like below.

2014 201401 1,723,791 71,825 71,825
2014 201402 1,575,715 209,304 209,304+71825
2014 201403 1,949,810 356,201 209,304+71825+356,201
2014 201404 1,879,966 515,775 209,304+71825+356,201+515,775
2014 201405 1,919,255 674,076 ...+674,076
2014 ... ... ... ...
2015 201501 2,764,606 2,148,709 2,148,709
2015 201502 2,072,820 4,087,748 2,148,709+4,087,748
2015 201503 1,816,945 4,160,573 2,148,709+4,087,748+4,160,573
2015 ... ... ... ...

Did I misunderstand your logic?

If it is convenient, please share your original data sample as table format so that I could understand your scenario better.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.