Average from the past 3 months

[mluanacruz]

Hello! 
I have a loss triangle where I am trying to find the ratio of the months. 

PLEASE SEE LINK HERE FOR THE EXCEL VERSION AND POWER BI 

I want my results to be column1/columns 0  = 8065/5108  column 2/column1 = 8520/8065 and so on.

I tried few different methods I found online but none of them worked. Does anyone has any idea how to do this?

Thanks for the help.

Michelle

[mluanacruz · Accepted solution]

I anyone is having the same question, here is what worked for me.

Ratio_Measure =

VAR DEV_MNT = MAX('PD_agg_perf_measures'[DEV_MONTHS])

VAR NuM = CALCULATE(SUM('PD_agg_perf_measures'[CNP_CNT_ACCOUNTING_VIEW]),FILTER(ALLSELECTED('PD_agg_perf_measures'),[DEV_MONTHS]= DEV_MNT&&[ACCIDENT_DATE_YYYYMM]=MAX('PD_agg_perf_measures'[ACCIDENT_DATE_YYYYMM])))

VAR DEN = CALCULATE(SUM('PD_agg_perf_measures'[CNP_CNT_ACCOUNTING_VIEW]),FILTER(ALLSELECTED('PD_agg_perf_measures'),[DEV_MONTHS]=DEV_MNT-1 &&[ACCIDENT_DATE_YYYYMM]=MAX('PD_agg_perf_measures'[ACCIDENT_DATE_YYYYMM])))

RETURN DIVIDE(NuM, DEN,0)

Hope it helps!!

[HoangHugo]

Hi

Call name of Column is Index column (should be whole number type)

please try this

Measure

Percentage = your measure/CALCULATE(your measure, Index column -1)

[mluanacruz]

Hi @HoangHugo 

Thanks for the answer! But I don't have any measure yet. This is strait from my excel workbook.
 I tried few different measures but none of them worked. Can you please help with the right measure?

I appreciate it!

Thank you!