Create list of years between two dates in new table

[minervag]

Hi All,

I have created a new table that takes two years and generates a series of values of as many years. Here is the first column:

Years = 
VAR MinDate = MIN(Projects[ProjectStartDate])
VAR MaxDate = MAX(Projects[ProjectFinishDate])
VAR ProjectTotalYears = DATEDIFF(MinDate, MaxDate,YEAR)
RETURN
GENERATESERIES(0, ProjectTotalYears, 1)

This results in the first columen "Value"

In the Year column, I would like to populate the years between MinDate and MaxDate. If the earliest date is in 2020 and the latest date is 2026, I would like to create the year column to populate with [2020, 2021, 2022, 2023, 2024, 2025, 2026], based on dates given. I was able to figure it out how to do it with months, but not years.

Any help would be great, thanks! 🙂

Minerva

[VahidDM · Accepted solution]

Hi @minervag 

Trt this code:

Years =
VAR _MinDate =
    MIN ( Projects[ProjectStartDate] )
VAR _MinYear =
    YEAR ( _MinDate )
VAR _MaxDate =
    MAX ( Projects[ProjectFinishDate] )
VAR _ProjectTotalYears =
    DATEDIFF ( _MinDate, _MaxDate, YEAR )
RETURN
    ADDCOLUMNS (
        GENERATESERIES ( 0, _ProjectTotalYears, 1 ),
        "Year", _MinYear + [Value]
    )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[minervag]

Hi VahidDM,

Thank you for responding! 🙂

I tried this solution and it gave me an error saying, "A table of multiple values was supplied where a single value was expected". I tried changing ADDCOLUMNS to SELECTCOLUMNS like @AlexisOlson suggested, but I got the same error.

Here is the error:

Minerva

[VahidDM]

@minervag 

It seems you used that code to add a new column, you need to use that code to add a new TABLE.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[minervag]

@VahidDM  - Never mind! I misunderstood and tried to add it as a seperate column. Once I replied to you, I thought it through and realized that I should try this code as a new table... and it worked! 

Thank you, thank you! I appreciate it. 🙂

Success:

[AlexisOlson]

@minervag BTW, if you want to omit the [Value] column from the resulting table, you can replace ADDCOLUMNS in @VahidDM's code with SELECTCOLUMNS.

[minervag]

Hi @AlexisOlson , I tried your idea of using SELECTCOLUMNS and it works well. Thanks! 🙂

Minerva