Summarize the first years of a table

[akasonia]

Good Morning, 
I have a table with data from 1999 till 2023. I would like to summarize only the first years (from 1999 to june 01,2019) of the table in Power BI.
The goal is to to have only the data before 06/01/2010 as one row per category.So, on 06/01/2019 we have one per category and this summary will be added on the rest of the data.
Account Number, Transaction Date, Amount, Description(charge,payment,adjustment,Penalties)
Here is a sample of the data.
Thank you for your help.

Date	Description	Sum of Amount
1/2/2019 0:00	Payment	-143.64
1/7/2019 0:00	Payment	-723.87
1/16/2019 0:00	Charge	184.93
1/17/2019 0:00	Charge	86.97
1/30/2019 0:00	Adjustment	538.94
2/1/2019 0:00	Payment	-271.9
2/14/2019 0:00	Charge	271.9
3/1/2019 0:00	Payment	-810.84
3/13/2019 0:00	Charge	120.79
3/14/2019 0:00	Charge	86.97
3/20/2019 0:00	Adjustment	538.94
3/29/2019 0:00	Payment	-86.97
4/5/2019 0:00	Payment	-861.96
4/10/2019 0:00	Charge	328.55
4/15/2019 0:00	Adjustment	538.94
4/22/2019 0:00	Payment	-86.97
5/8/2019 0:00	Adjustment	538.94
5/9/2019 0:00	Payment	-780.52
5/16/2019 0:00	Charge	241.58
6/5/2019 0:00	Adjustment	538.94
6/13/2019 0:00	Charge	241.58
6/17/2019 0:00	Payment	-1319.46
6/26/2019 0:00	Payment	-912.47
7/11/2019 0:00	Charge	241.58
7/17/2019 0:00	Adjustment	1074.63
8/2/2019 0:00	Adjustment	88.26
8/8/2019 0:00	Payment	-241.58
8/14/2019 0:00	Charge	260.91
8/15/2019 0:00	Adjustment	560.58
8/30/2019 0:00	Payment	-1984.38
9/12/2019 0:00	Charge	260.91
9/30/2019 0:00	Adjustment	560.58
10/10/2019 0:00	Payment	-260.91
10/16/2019 0:00	Charge	260.91
11/1/2019 0:00	Payment	-821.49
11/6/2019 0:00	Adjustment	560.58
11/14/2019 0:00	Charge	260.91
11/27/2019 0:00	Payment	-821.49
12/10/2019 0:00	Adjustment	1074.63
12/11/2019 0:00	Charge	260.91
1/3/2020 0:00	Payment	-1335.54
1/14/2020 0:00	Charge	260.91
1/23/2020 0:00	Adjustment	560.58
2/7/2020 0:00	Payment	-260.91
2/11/2020 0:00	Charge	260.91
3/5/2020 0:00	Payment	-821.49
3/11/2020 0:00	Charge	260.91
3/25/2020 0:00	Adjustment	560.58
4/6/2020 0:00	Payment	-260.91
4/14/2020 0:00	Charge	260.91
4/27/2020 0:00	Payment	-821.49
5/12/2020 0:00	Charge	260.91
6/9/2020 0:00	Charge	260.91
6/10/2020 0:00	Payment	-260.91
6/25/2020 0:00	Payment	-260.91
7/7/2020 0:00	Adjustment	560.58
7/13/2020 0:00	Charge	260.91
8/5/2020 0:00	Payment	-821.49
8/13/2020 0:00	Charge	260.91
8/24/2020 0:00	Adjustment	560.58
8/27/2020 0:00	Payment	-260.91
9/17/2020 0:00	Charge	260.91
9/30/2020 0:00	Payment	-821.49
10/13/2020 0:00	Charge	260.91
10/16/2020 0:00	Adjustment	560.58
10/30/2020 0:00	Payment	-260.91
11/10/2020 0:00	Charge	260.91
11/30/2020 0:00	Payment	-821.49
12/8/2020 0:00	Charge	260.91
12/11/2020 0:00	Adjustment	560.58
12/23/2020 0:00	Payment	-260.91
1/13/2021 0:00	Charge	260.91
2/4/2021 0:00	Payment	-821.49
2/10/2021 0:00	Charge	260.91
2/12/2021 0:00	Adjustment	560.58
2/25/2021 0:00	Payment	-260.91
3/9/2021 0:00	Charge	260.91
3/18/2021 0:00	Payment	-821.49
4/6/2021 0:00	Adjustment	560.58
4/14/2021 0:00	Charge	260.91
5/5/2021 0:00	Payment	-821.49
5/11/2021 0:00	Charge	260.91
5/24/2021 0:00	Adjustment	560.58
5/25/2021 0:00	Payment	-260.91
6/15/2021 0:00	Charge	260.91
7/7/2021 0:00	Payment	-821.49
7/13/2021 0:00	Charge	260.91
7/14/2021 0:00	Adjustment	1190.31
8/11/2021 0:00	Payment	-1451.22
8/19/2021 0:00	Charge	285.42
9/8/2021 0:00	Payment	-285.42
9/13/2021 0:00	Adjustment	1190.31
9/14/2021 0:00	Charge	285.42
10/6/2021 0:00	Payment	-1475.73
10/13/2021 0:00	Charge	285.42
10/29/2021 0:00	Adjustment	1190.31
11/3/2021 0:00	Payment	-285.42
11/10/2021 0:00	Charge	285.42
12/8/2021 0:00	Payment	-1475.73
12/14/2021 0:00	Charge	285.42
1/4/2022 0:00	Adjustment	1190.31
1/5/2022 0:00	Payment	-285.42
1/12/2022 0:00	Charge	285.42
1/24/2022 0:00	Adjustment	1190.31
2/1/2022 0:00	Adjustment	1190.31
2/9/2022 0:00	Payment	-1475.73
2/16/2022 0:00	Charge	285.42
3/9/2022 0:00	Payment	-2666.04
3/16/2022 0:00	Charge	285.42
3/21/2022 0:00	Adjustment	0
4/6/2022 0:00	Payment	-285.42
4/12/2022 0:00	Charge	285.42
5/4/2022 0:00	Payment	-285.42
5/10/2022 0:00	Charge	285.42
5/13/2022 0:00	Adjustment	1190.31
6/8/2022 0:00	Payment	-285.42
6/15/2022 0:00	Charge	285.42
7/13/2022 0:00	Charge	285.42
7/21/2022 0:00	Adjustment	1279.58
8/17/2022 0:00	Charge	306.82
8/31/2022 0:00	Adjustment	1279.58
9/13/2022 0:00	Charge	306.82
10/6/2022 0:00	Adjustment	1279.58

[rajendraongole1]

Hi @akasonia  - Create a new table with summary before2019

SummaryBeforeJune2019 =
SUMMARIZE(
FILTER(
OriginalData,
OriginalData[Date] <= DATE(2019, 6, 1)
),
OriginalData[Category],
"SummarizedValue", SUM(OriginalData[Value])
)

create another table after 2019

DataAfterJune2019 =
FILTER(
OriginalData,
OriginalData[Date] > DATE(2019, 6, 1)
)

and combine both as final table using union

CombinedData =
UNION(
SummaryBeforeJune2019,
SELECTCOLUMNS(DataAfterJune2019, "Category", DataAfterJune2019[Category], "SummarizedValue", DataAfterJune2019[Value])
)

Ensure that the SummarizedValue and Date columns do not conflict in your CombinedData table. If necessary, adjust the schema or column names to avoid ambiguity.
This approach assumes that the value to be summarized is Value. Adjust the column names and DAX expressions to match your actual dataset.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[akasonia]

Thank you @rajendraongole1 ,
I started craeted the summury before 2019 and I would like to add a date column that will have 06/01/20119 as date (Please refer to the table I added below), but my DAX is wrong. . Here is the DAX:

SummaryBeforeJune2019 =

SUMMARIZE(

FILTER(

'Combined Table',

'Combined Table'[Date] <= DATE(2019, 6, 1)

),

'Combined Table'[Description],

'Combined Table'[Account Number],

'Combined Table'[Date]=2019,6,1, 

"Amount", SUM('Combined Table'[Amount])

I would like to add a date column that will have 06/01/20119 as date like the table below:

Account Number	Description	Amount	Combined Date
26364	Charge	40963.11	6/1/2019
15326	Charge	4519.4	6/1/2019
15326	Adjustment	317.47	6/1/2019
26364	Adjustment	-121.74	6/1/2019
15326	Payment	-10847.14	6/1/2019
26364	Payment	-121181.65	6/1/2019

[Anonymous · Accepted solution]

Hi @akasonia ,

Based on the data you've given, I think you can start by creating a Measure to pull out the dates before 2019 and 2019 itself.

Measure = 
SUMX (
    FILTER ( 'Table', YEAR ( 'Table'[Date] ) <= 2019 ),
    'Table'[Sum of Amount]
)

Then you can use slicer to get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.