Get duration in days of event per year

[kbuckvol]

I have a  collection of projects that begin and end at different dates, some within the same year and some span several years.  I need a way quickly create a matrix that shows how many days within each year the project occured.  I'm sure this is a filter of some kind, but I'm struggling to put it together.  The ultimate goal is to create another matrix that shows the percentage of days within each year that the project fell in.  In my sample table, I have the first four left columns, but I'm trying to figure out the next five.

Project	Begin	End	duration	2022	2023	2024	2025	2026
A	1/10/2022	4/30/2022	110	110
B	11/16/2023	5/29/2024	195		45	150
C	5/16/2024	1/15/2025	244			229	15
D	9/19/2024	1/9/2026	477			103	365	9
E	9/19/2024	12/22/2025	459			103	356
F	12/19/2024	12/29/2026	740			12	365	363
G	4/21/2025	9/30/2025	162				162
H	5/1/2025	12/30/2025	243				243

Project	Begin	End	duration	2022	2023	2024	2025	2026
A	1/10/2022	4/30/2022	110	100%
B	11/16/2023	5/29/2024	195		23%	77%
C	5/16/2024	1/15/2025	244			94%	6%
D	9/19/2024	1/9/2026	477			22%	77%	2%
E	9/19/2024	12/22/2025	459			22%	78%
F	12/19/2024	12/29/2026	740			2%	49%	49%
G	4/21/2025	9/30/2025	162				100%
H	5/1/2025	12/30/2025	243				100%

[bolfri · Accepted solution]

Sample data that I have used:

Step 1: Create dim_calendar that contains all the dates from Jan 1st from first year in your data till Dec 31th in last year in your data

dim_calendar =

CALENDAR(

    DATE(YEAR(FIRSTDATE('Sample'[Status Start Date]));1;1);

    DATE(YEAR(LASTDATE('Sample'[Status End Date]));12;31)

)

Step 2: Add a year column in this table

Year = YEAR([Date])

Step 3: Create count days measure

We know from context that each row is a one day.

Count days = COUNTROWS(dim_calendar)

Step 4. Viz how many days we have got in each year

Step 5. Calculate program duration measure
min_date > find min_date of Start date for program defined in Sample table

max_date > find max_date of Max date for program defined in Sample table

Note that this can be used to provide agregation for programs, which starts and ends in different dates.

Program duration =

var min_date = MINX('Sample';FIRSTDATE('Sample'[Status Start Date]))

var max_date = MAXX('Sample';LASTDATE('Sample'[Status End Date]))

return

    CALCULATE(

        [Count days];

        DATESBETWEEN(dim_calendar[Date];min_date;max_date))

Bonus: Define Percentage days for each year

Percentage days = DIVIDE([Program duration];[Count days])

Result: