cancel
Showing results for
Did you mean:
New Member

## Create list of years between two dates in new table

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

1 ACCEPTED SOLUTION
Community Champion

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
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.

6 REPLIES 6
Community Champion

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
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.

New Member

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

Community Champion

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.

New Member

@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:

Super User

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

New Member

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

Minerva

Announcements