Forum Discussion

mwatson4788's avatar
mwatson4788
Frequent Visitor
4 years ago

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 com...
mwatson4788's avatar
mwatson4788
Frequent Visitor
4 years ago

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