Need Help with Half on Half and Year on Year Movements with Financial Data

[HagenHawke]

Hi Community,

I am very new to Power BI. I just completed my Power BI Beginners and Intermediate courses 3 weeks ago and taking on a small project within my team to convert one report from excel to Power BI. I am the first person in my wider team to use Power BI and really in need of some guidance.

My data has half year and full year results for few companies and I want to build some smarts in my dashboard where users can filter to different periods and see the movements between those selected periods.

For example:

They can select 1H18 v 1H19 or 1H18 v 2H19 and ablet to see the movement between this period.

Below is a sample data of what my data looks like.

Category	Company	Period	Name	Value
Earnings and Returns	A	1H18	Cash earnings	1,200
Earnings and Returns	A	1H19	Cash earnings	2,300
Earnings and Returns	A	2H18	Cash earnings	1,108
Earnings and Returns	A	2H19	Cash earnings	2,208
Earnings and Returns	A	FY18	Cash earnings	2,308
Earnings and Returns	A	FY19	Cash earnings	4,508
Earnings and Returns	C	1H18	Cash earnings	121
Earnings and Returns	C	1H19	Cash earnings	209
Earnings and Returns	C	2H18	Cash earnings	56
Earnings and Returns	C	2H19	Cash earnings	269
Earnings and Returns	C	FY18	Cash earnings	177
Earnings and Returns	C	FY19	Cash earnings	478
Earnings and Returns	E	1H18	Cash earnings	1,251
Earnings and Returns	E	1H19	Cash earnings	2,586
Earnings and Returns	E	2H18	Cash earnings	2,834
Earnings and Returns	E	2H19	Cash earnings	2,968
Earnings and Returns	E	FY18	Cash earnings	4,085
Earnings and Returns	E	FY19	Cash earnings	5,554
Revenues	A	1H18	Net Interest Income	5,000
Revenues	A	1H19	Net Interest Income	6,200
Revenues	A	2H18	Net Interest Income	5,212
Revenues	A	2H19	Net Interest Income	6,225
Revenues	A	FY18	Net Interest Income	10,212
Revenues	A	FY19	Net Interest Income	12,425
Revenues	C	1H18	Net Interest Income	321
Revenues	C	1H19	Net Interest Income	569
Revenues	C	2H18	Net Interest Income	321
Revenues	C	2H19	Net Interest Income	520
Revenues	C	FY18	Net Interest Income	642
Revenues	C	FY19	Net Interest Income	1,089
Revenues	E	1H18	Net Interest Income	7,215
Revenues	E	1H19	Net Interest Income	6,958
Revenues	E	2H18	Net Interest Income	7,023
Revenues	E	2H19	Net Interest Income	6,213
Revenues	E	FY18	Net Interest Income	14,238
Revenues	E	FY19	Net Interest Income	13,171
Expenses	A	1H18	Total Operating Expenses	-2,554
Expenses	A	1H19	Total Operating Expenses	-2,431
Expenses	A	2H18	Total Operating Expenses	-2,727
Expenses	A	2H19	Total Operating Expenses	-2,518
Expenses	A	FY18	Total Operating Expenses	-5,281
Expenses	A	FY19	Total Operating Expenses	-4,949
Expenses	C	1H18	Total Operating Expenses	-621
Expenses	C	1H19	Total Operating Expenses	-325
Expenses	C	2H18	Total Operating Expenses	-423
Expenses	C	2H19	Total Operating Expenses	-369
Expenses	C	FY18	Total Operating Expenses	-1,044
Expenses	C	FY19	Total Operating Expenses	-694
Expenses	E	1H18	Total Operating Expenses	-4,215
Expenses	E	1H19	Total Operating Expenses	-4,689
Expenses	E	2H18	Total Operating Expenses	-3,689
Expenses	E	2H19	Total Operating Expenses	-4,578
Expenses	E	FY18	Total Operating Expenses	-7,904
Expenses	E	FY19	Total Operating Expenses	-9,267

I totally appreciate your help.

Warm regards,

HagenHawke

[v-jayw-msft · Accepted solution]

Hi @HagenHawke ,

If you want it be dynemic, you will need to create two slicer tables as below:

slicer1 = DISTINCT('Table'[Period])

slicer2 = DISTINCT('Table'[Period])

Then create a measure like below:

Measure = 
var selected1 = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Company],'Table'[Name]),'Table'[Period]=SELECTEDVALUE(slicer1[Period])))
var selected2 = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Company],'Table'[Name]),'Table'[Period]=SELECTEDVALUE(slicer2[Period])))
return
(selected1-selected2)/selected2

Since it's a measure, it will be generated under each column if you put it into value field.

Then you could turn off the text wrap and hide the extra columns

Best Regards,

Jay

[HagenHawke]

Thank you so much for taking your time to show me this. This is very helpful for a Power BI Beginner like me. I totally appreciate it.

Kind regards,

M

[HagenHawke]

The out put would look something like below:

                            User would select different   periods like the ones  highlighted in 'orange' to see the growth between periods.

Company	Name	1H18	1H19	2H18	2H19	FY18	FY19	1H19 v 2H18	1H19 v 1H18	2H19 v 1H19	2H19 v 2H18	FY19 v FY18
A	Cash earnings	1,200	2,300	1,108	2,208	2,308	4,508	107.6%	91.7%	-4.0%	99.3%	95.3%
A	Net Interest Income	5,000	6,200	5,212	6,225	10,212	12,425	19.0%	24.0%	0.4%	19.4%	21.7%
A	Total Operating Expenses	-      2,554	-             2,431	-      2,727	-        2,518	-               5,281	-        4,949	-10.9%	-4.8%	3.6%	-7.7%	-6.3%
C	Cash earnings	121	209	56	269	177	478	273.2%	72.7%	28.7%	380.4%	170.1%
C	Net Interest Income	321	569	321	520	642	1,089	77.3%	77.3%	-8.6%	62.0%	69.6%
C	Total Operating Expenses	-         621	-                325	-         423	-            369	-               1,044	-            694	-23.2%	-47.7%	13.5%	-12.8%	-33.5%
E	Cash earnings	1,251	2,586	2,834	2,968	4,085	5,554	-8.8%	106.7%	14.8%	4.7%	36.0%
E	Net Interest Income	7,215	6,958	7,023	6,213	14,238	13,171	-0.9%	-3.6%	-10.7%	-11.5%	-7.5%
E	Total Operating Expenses	-      4,215	-             4,689	-      3,689	-        4,578	-               7,904	-        9,267	27.1%	11.2%	-2.4%	24.1%	17.2%

[danextian]

Hi @HagenHawke ,

I am not sure what you mean by movements but you can certainly create a measure that will get the difference between two periods. Please refer to the this pbix -Difference Between Two Periods that are not Date Time Keys 

[jsaunders_zero9]

Hi @HagenHawke 

Can you sketch on paper what you expect the end result to be? This will help someone assist you.

Thank you