cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

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

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?

1 ACCEPTED SOLUTION
Super User

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

Proud to be a Super User!

Super User

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

Proud to be a Super User!