Cumulatively Count Years or Months between Dates

[JB_AT]

Hello

I have a table with columns

DateFrom

DateTo

ID

DateFrom and DateTo have a inactive relationship with a DIM Date Table. 

In a visual, I would like to show the cumulative years over time. Below is the table structure

ID	DateFrom	DateTo
1	01/01/2020	30/03/2021
1	01/04/2021	31/10/2022
1	01/11/2022	31/12/2023

I would like the result to look like this

Year	Culmative Year
2020	1
2021	2
2022	3
2023	4

Thanks

[some_bih]

Hi @JB_AT you can create calculated column like below to find difference between 2 columns in Years.

Still, I do not understand your logic to get expected results. Provide more details / model.

calculated column (adjust your table name)

Diff in Years = DATEDIFF(<Your table name>[DateFrom],<Your table name>[DateTo],YEAR)

[some_bih]

Hi @JB_AT DateFrom 01/01/2020 is always fix and cumulative should calculate like (DateTo - fixed 01/01/2020)?

[JB_AT]

Hi @some_bih 

The dates aren't fixed. They would be different for other IDs. The result table below, is if we were just looking at ID 1. So it's basically calculating the Years between DateFrom and DateTo on each row, then breaking these out into years per ID. Hope that makes sense

Year	Culmative Year
2020	1
2021	2
2022	3
2023	4