Count the SUM of a column GROUPED BY id, and sorted by year

[msuser48]

I have this table:

RowId	Id	StartDate	MailDate	Trips
1	1	2019-11-20	2020-11-20	4
2	1	2019-11-20	2020-12-10	1
3	1	2019-11-20	2022-12-10	3
4	2	2018-11-20	2021-01-01	6
5	2	2018-11-20	2021-02-02	7
6	3	2019-01-01	2020-01-01	2
7	3	2019-01-01	2022-05-05	2
8	3	2019-01-01	2022-06-09	3
9	4	2015-01-01	2019-01-01	2

What I need to do:

I need the SUM of Trips made after each year since each Ids StartDate.

The output needs to be this table, which would look like this for the above table:

YearsPassedSinceStartDate (0-5 years is fixed interval)	Amount (this will be our calculated column)
0	0
1	7
2	13
3	8
4	2
5	0

For example, for YearsPassedSinceStartDate with the value "1", we know that the amount is = 7.

- Why? Because RowId 1, 2 and 6's DateDiff between MailDate and StartDate is 1 year. And SUM of their trips is 4+1+2 = 7.

How do I produce the above table's calculated "Amount" column like this?

[ryan_mayu · Accepted solution]

@msuser48 

you can create a column

Column = int( DATEDIFF('Table'[StartDate],'Table'[MailDate],month)/12)

create a year table

and create a measure

Measure = sumx(FILTER('Table',max('Table (2)'[year])='Table'[Column]),'Table'[Trips])+0

pls see the attachment below