cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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

Accepted Solutions
Highlighted
Resident Rockstar
Resident Rockstar

Re: Calculate Monthly Earned Premium and Sum by YTD

Hi @tpln201 ,

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
Highlighted
Resident Rockstar
Resident Rockstar

Re: Calculate Monthly Earned Premium and Sum by YTD

Hi @tpln201 ,

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.
Highlighted
Resident Rockstar
Resident Rockstar

Re: Calculate Monthly Earned Premium and Sum by YTD

Hi @tpln201 ,

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

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors