topic Create list of years between two dates in new table in DAX Commands and Tips

Create list of years between two dates in new table

minervag — Tue, 30 Nov 2021 02:02:27 GMT

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

Re: Create list of years between two dates in new table

VahidDM — Tue, 30 Nov 2021 02:28:25 GMT

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/

Re: Create list of years between two dates in new table

AlexisOlson — Tue, 30 Nov 2021 02:36:37 GMT

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

Re: Create list of years between two dates in new table

minervag — Wed, 01 Dec 2021 00:30:26 GMT

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

Re: Create list of years between two dates in new table

minervag — Wed, 01 Dec 2021 00:36:09 GMT

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:

Re: Create list of years between two dates in new table

minervag — Wed, 01 Dec 2021 00:36:55 GMT

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

Minerva

Re: Create list of years between two dates in new table

VahidDM — Wed, 01 Dec 2021 00:37:53 GMT

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/