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.
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 |
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
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!
Hi
Try this
thank you I will try this and let you know
After an extensive Google search I came up with a solution. Here is the formula:
I want to calculate the variance between these points.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |