cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
minervag
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"

minervag_0-1638237425112.png

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
VahidDM
Community Champion
Community Champion

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:

VahidDM_0-1638239295384.png

 

 

 

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/

 

 

View solution in original post

6 REPLIES 6
VahidDM
Community Champion
Community Champion

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:

VahidDM_0-1638239295384.png

 

 

 

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/

 

 

View solution in original post

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:

 

minervag_0-1638318528518.png

 

 

Minerva

VahidDM
Community Champion
Community Champion

@minervag 

 

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

 

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/

 

 



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

minervag_1-1638318953487.png

 

 

 

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

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

 

Minerva

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors