Calculate Monthly Earned Premium and Sum by YTD

[Anonymous]

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

[v-piga-msft · Accepted solution]

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

[v-piga-msft]

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