Sum of data for prior years in Power BI

[jeesan1234]

I have the following data:

YOA	Incurred	Paid	Outstanding
2002	-2	-2	0
2003	-2	1	-3
2004	-186	-203	17
2005	-80	-21	-59
2006	-60	-33	-26
2007	-540	-526	-14
2008	-222	15	-237
2009	-349	4	-353
2010	-165	900	-1065
2011	111	104	-508
2012	-989	-1713	-3954
2013	2221	2222	221
2014	1785	2171	-386
2015	317	122	-6028
2016	2920	5736	-2816
2017	-12729	-3377	-9351
2018	2324	15722	24
2019	11403	121	33
2020	23	45949	-26915
2021	59868	84095	33
2022	122	23	233
2023	3565	656566	86164
2024	5666	5656	5656

However, I want to combine the data for everything prior 2008 so it looks like:

YOA	Incurred	Paid	Outstanding
2008 and Prior	-1092	-769	-322
2009	-349	4	-353
2010	-165	900	-1065
2011	111	104	-508
2012	-989	-1713	-3954
2013	2221	2222	221
2014	1785	2171	-386
2015	317	122	-6028
2016	2920	5736	-2816
2017	-12729	-3377	-9351
2018	2324	15722	24
2019	11403	121	33
2020	23	45949	-26915
2021	59868	84095	33
2022	122	23	233
2023	3565	656566	86164
2024	5666	5656	5656

How do I do this in Power BI? 

[rajendraongole1 · Accepted solution]

Hi @jeesan1234 - 

add a custom column in power query editor as below:

After adding the conditional column, go to the Home tab and click Group By.Select the new column (with "2008 and Prior" and other YOA values).Choose the Sum operation for the Incurred, Paid, and Outstanding columns.

final output as below:

Hope this helps

[_AAndrade · Accepted solution]

Hi @jeesan1234,

I'm attaching my solution using Power Query.
I hpoe this could help you.

[rajendraongole1 · Accepted solution]

Hi @jeesan1234 - 

add a custom column in power query editor as below:

After adding the conditional column, go to the Home tab and click Group By.Select the new column (with "2008 and Prior" and other YOA values).Choose the Sum operation for the Incurred, Paid, and Outstanding columns.

final output as below:

Hope this helps

[Kedar_Pande]

Click on the Modeling tab in the ribbon and select New Table.

CombinedData =
UNION(
SELECTCOLUMNS(
FILTER(YourTable, YourTable[YOA] < 2008),
"YOA", "2008 and Prior",
"Incurred", SUM(YourTable[Incurred]),
"Paid", SUM(YourTable[Paid]),
"Outstanding", SUM(YourTable[Outstanding])
),
SELECTCOLUMNS(
FILTER(YourTable, YourTable[YOA] >= 2008),
"YOA", YourTable[YOA],
"Incurred", YourTable[Incurred],
"Paid", YourTable[Paid],
"Outstanding", YourTable[Outstanding]
)
)

If this helped, a Kudos 👍 or Solution mark would be great!
Cheers,
Kedar Pande
www.linkedin.com/in/kedar-pande