cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

6 REPLIES 6
Frequent Visitor

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

Solution Sage

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

thank you I will try this and let you know

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

Frequent Visitor

I want to calculate the variance between these points.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors