Dax Help to split years between two dates using Dax or Power Query

[Anonymous]

Hi Experts , How can I split years between two dates using Dax or Power Query

Start Date	End Date	Output
01-01-2021	01-01-2023	2021, 2022, 2023

[AmiraBedh]

Using Power Query :

let 
    SplitYears = (StartDate, EndDate) => 
let 
    StartYear = Date.Year(StartDate),
    EndYear = Date.Year(EndDate),
    ListYears = {StartYear..EndYear},
    Result = Table.FromList(ListYears, Splitter.SplitByNothing())
in 
    Result
in 
    SplitYears

Now you can use this function in your main query  : 

Spoiler

SplitYears([Start Date],[End Date])

Using DAX :

You can create a calculated table with the following DAX formula:

YearTable = 
    ADDCOLUMNS(
        CALENDAR(
            YEAR(MIN(Table[Start Date])),
            YEAR(MAX(Table[End Date]))
        ), 
        "Year", YEAR([Date])
    )

Then create a calculated column in your main table that concatenates the years for each row. You could use the CONCATENATEX function to do this :

YearList = 
    CALCULATE(
        CONCATENATEX(
            YearTable,
            YearTable[Year],
            ", "
        ),
        YearTable[Year] >= YEAR(Table[Start Date]),
        YearTable[Year] <= YEAR(Table[End Date])
    )