Report based on earliest date (year)

[AB-Learner]

I have a table with a reference field (multiple values).  For each record, there is a date field.  The date field, for the same value of a reference field, can have different values.

Example:

Reference field	Date
A123	2022-03-17
A123	2022-05-15
A123	2023-09-14
A123	2024-01-25
A123	2024-02-18
A058	2021-07-15
A982	2024-04-23
A587	2022-03-21
A982	2024-03-28
A587	2024-01-15

I want to have a report that shows me for each "reference field" value, the first year in which it appeared.  And then a total per year.  Based on the above table, the output would be as follows:

Reference field	2021	2022	2023	2024
A058	1
A123		1
A587		1
A982				1
TOTALS	1	2		1

Being a newbie in PowerBI related queries/tables/reports, the above simple need is working out to be quite difficult for me.  Any help towards this is appreciated.

[Ahmedx · Accepted solution]

pls try this

[AB-Learner]

Thanks.

This solution works well.  Of course, I have not fully understood the logic but that is because I do not know fully well what does the various functions (SUMX, SUMMARIZE, ...) do.  I will do my self-reading to understand those functions - but I confirm that your proposed solution works well for my need.