YoY Calculation

[mwatson4788]

I have a snapshot table that gets a policy in force count(pif) at the end of every month.

I need to know how to calculate Year over year change.  So in the data below for April 2022, I 

want to compare Total_PIF against Total_PIF in April 2021 to see the difference. 

How do I calculate YoY change using this data model.

My data looks like this

report_date    Total_PIF

1/1/2020	4348
2/1/2020	4280
3/31/2020	4210
4/1/2020	4147
5/1/2020	4118
6/1/2020	4098
7/31/2020	3915
8/31/2020	4000
9/30/2020	3974
10/31/2020	3939
11/30/2020	3889
12/31/2020	3827
1/31/2021	3782
2/28/2021	3746
3/31/2021	3723
4/30/2021	3696
5/31/2021	3663
6/30/2021	3633
7/21/2021	3580
8/31/2021	3529
9/30/2021	3490
10/31/2021	3442
11/30/2021	3419
12/31/2021	3408
1/31/2022	3363
2/28/2022	3340
3/31/2022	3293
4/30/2022	3243

[mwatson4788]

ok, I am making progress, this is what I have so far:   

So what this means is that since April 2016 the total policy count has went down 5742 policies.

What I want to do now is divide the 5742 by the beginning total pif of 8985 to get the total % decrease.

I can't figure out how to do this.  Any help would be appreciated.  Thank you fo the help so far.

Mark

[Whitewater100]

Hi Mark:

maybe something like:

Total PIF YoY Policy Change = CALCULATE([Total PIF Yoy Change], REMOVEFILTERS()) // to obtain -5,742

Total YoY % Chg = 
var StartValue= MAX(Table[Total_PIF])
return

DIVIDE([Total PIF YoY Policy Change], StartValue,0)

The variable StartValuemay have to be played with.

Another way to write the StartValue variable:

MAXX(Table, [Total_PIF])

or CALCULATE[Total_PF], FILTER(ALL(Table), Table[Month-Year] = "April-2016'))

I think this should be good. I hope!

[Anonymous]

Hi

Try this 

sales Actual=calculate(sum(sales[sales])

sales year PY=calculate([sales Actual],SAMEPERIODLASTYEAR('Date'[Date]))

[mwatson4788]

thank you I will try this and let you know

[mwatson4788]

After an extensive Google search I came up with a solution.  Here is the formula: 

Total_PIF_YoY_Change = if(MRPIUA_PIF_TIV_History[report_date].[Year]=2016,0, Var Diff = calculate(sum(MRPIUA_PIF_TIV_History[Total_PIF]),filter('MRPIUA_PIF_TIV_History','MRPIUA_PIF_TIV_History'[Index]=EARLIER(MRPIUA_PIF_TIV_History[index])-12)) return if(diff=Value('MRPIUA_PIF_TIV_History'[Total_PIF]),0,diff)-MRPIUA_PIF_TIV_History[Total_PIF])

The data goes back to 2016 that's why I set the value to 0 for 2016.  My first question is this,  How can I set the value to null instead of 0, it says null is not suported in this context.  I get the reasoning just don't know how to fix this.  Also, how do I set the differences to negative numbers since they are going down?

Thanks

[mwatson4788]

I want to calculate the variance between these points.