cancel
Showing results for
Did you mean:

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

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

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 Year Report Month Total Premium Before Tax Accident Earned Premium 2014 201411 1,993,557 1,837,995 2014 201412 2,211,388 2,013,201 2014 25,264,105 2015 201501 2,764,606 2,148,709 2015 201502 2,072,820 4,087,748 2015 201503 1,816,945 4,160,573 2015 201504 2,553,056 4,204,405 2015 201505 2,017,313 4,175,194 2015 201506 2,265,562 4,233,950 2015 201507 2,244,269 4,201,482 2015 201508 1,985,554 4,177,781 2015 201509 2,094,842 4,171,563 2015 201510 2,178,200 4,126,148 2015 201511 2,045,619 4,109,171 2015 201512 2,346,595 4,128,022 2015 26,385,380

What I need

 Report Year Report Month Total Premium Before Tax Accident Earned Premium YTD Accident Earned Premium 2014 201401 1,723,791 71,825 71,825 2014 201402 1,575,715 209,304 209,304 2014 201403 1,949,810 356,201 356,201 2014 201404 1,879,966 515,775 515,775 2014 201405 1,919,255 674,076 674,076 2014 201406 2,024,968 838,418 838,418 2014 201407 2,767,593 1,038,108 1,038,108 2014 201408 2,073,645 1,239,827 2014 201409 2,673,410 1,437,621 2014 201410 2,471,006 1,651,971 2014 201411 1,993,557 1,837,995 2014 201412 2,211,388 2,013,201 2014 25,264,105 11,884,322 3,703,707 2015 201501 2,764,606 2,148,709 2,148,709 2015 201502 2,072,820 4,087,748 4,087,748 2015 201503 1,816,945 4,160,573 4,160,573 2015 201504 2,553,056 4,204,405 4,204,405 2015 201505 2,017,313 4,175,194 4,175,194 2015 201506 2,265,562 4,233,950 4,233,950 2015 201507 2,244,269 4,201,482 4,201,482 2015 201508 1,985,554 4,177,781 2015 201509 2,094,842 4,171,563 2015 201510 2,178,200 4,126,148 2015 201511 2,045,619 4,109,171 2015 201512 2,346,595 4,128,022 2015 26,385,380 47,924,745 27,212,060

1 ACCEPTED SOLUTION
Resident Rockstar

Hi @Anonymous ,

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.

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.
2 REPLIES 2
Resident Rockstar

Hi @Anonymous ,

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.

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

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

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors