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

  • 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
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi

    Try this 

    sales Actual=calculate(sum(sales[sales])
    sales year PY=calculate([sales Actual],SAMEPERIODLASTYEAR('Date'[Date]))
  • mwatson4788's avatar
    mwatson4788
    Frequent Visitor
    4 years ago

    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's avatar
      Whitewater100
      Solution Sage
      4 years ago

      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!