Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!

Anonymous
Not applicable

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.