topic Re: YoY Calculation in DAX Commands and Tips

YoY Calculation

mwatson4788 — Thu, 19 May 2022 16:18:02 GMT

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

Re: YoY Calculation

mwatson4788 — Thu, 19 May 2022 16:48:01 GMT

I want to calculate the variance between these points.

Re: YoY Calculation

mwatson4788 — Thu, 19 May 2022 19:29:23 GMT

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

Re: YoY Calculation

Anonymous — Thu, 19 May 2022 20:34:56 GMT

Try this

sales Actual=calculate(sum(sales[sales])

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

Re: YoY Calculation

mwatson4788 — Thu, 19 May 2022 22:17:06 GMT

thank you I will try this and let you know

Re: YoY Calculation

mwatson4788 — Thu, 19 May 2022 22:24:11 GMT

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

Re: YoY Calculation

Whitewater100 — Fri, 20 May 2022 00:32:12 GMT

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!