cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mwatson4788
Frequent Visitor

YoY Calculation

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/20204348
2/1/20204280
3/31/20204210
4/1/20204147
5/1/20204118
6/1/20204098
7/31/20203915
8/31/20204000
9/30/20203974
10/31/20203939
11/30/20203889
12/31/20203827
1/31/20213782
2/28/20213746
3/31/20213723
4/30/20213696
5/31/20213663
6/30/20213633
7/21/20213580
8/31/20213529
9/30/20213490
10/31/20213442
11/30/20213419
12/31/20213408
1/31/20223363
2/28/20223340
3/31/20223293
4/30/20223243

 

6 REPLIES 6
mwatson4788
Frequent Visitor

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

mwatson4788_0-1652998889447.png

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

 

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!

JamesFr06
Super User
Super User

Hi

Try this 

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

thank you I will try this and let you know

mwatson4788
Frequent Visitor

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

mwatson4788_0-1652978828251.png

I want to calculate the variance between these points.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors