cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pierre415
Helper I
Helper I

Create a List of Years

I need a list of the past 5 years in a new table, so the column would have (2013 ... 2016, 2017). List.Dates seems like the perfect function for this except the duration functions that it requires only have intervals of days/hrs/minutes as far as I can tell. Any way of doing this by way of formula instead of manually?

 

Thanks

Pierre

1 ACCEPTED SOLUTION

Hi @pierre415,

 

Based on my test, you should also be able to use the formula(DAX) below to create a new calculate table to make an automated list of past 5 years that updates itself over the years. Smiley Happy

Year = 
DISTINCT (
    SELECTCOLUMNS (
        CALENDAR ( DATE ( YEAR ( TODAY () ) - 4, 1, 1 ), TODAY () ),
        "Year", YEAR ( [Date] )
    )
)

c1.PNG

 

Regards

View solution in original post

5 REPLIES 5
TheOckieMofo
Resolver II
Resolver II

For something this straightforward, you can manually create a table. Simply click on "Enter Data" on the top ribbon of Power Bi and it will allow you to manually enter a small dataset like this.

 

If you want/need something fancier without getting into utilizing complex M functions (M is great but sometimes the heavy lifting is better off being done by the big boys such as Chris Webb instead of mere mortals like us), you can use the "New Table" functionality that is in Power BI. So for example, if the data you need for a look up table is contained within your fact table, you could create a dynamic table by using some of the "table" functions of DAX. So in this case, if all 5 years you need are con[tained in your fact table, you can simply do the following:

 

1. Select "modeling" tab

2. Select "New Table"

3. In formula bar, create new table with following syntax:

Year Table = ALL('FACT_TABLE_NAME'[YEAR])

 

That's it. If you want more details on the create table functionality, there's a great blog post by @MattAllington (whom you will see frequently posting on this forum and all around the web on the Microsoft BI Stack) on PowerPivotPro here: Table Function

 

Whew, that's a lot. I hope this helps. Good luck.

 

 

GilesWalker
Skilled Sharer
Skilled Sharer

@pierre415 - You could use the enter data function in the deskptop ribbon.

 

Giles

Thanks, I'm looking to make an automated list that updates itself over the years though

Hi @pierre415,

 

Based on my test, you should also be able to use the formula(DAX) below to create a new calculate table to make an automated list of past 5 years that updates itself over the years. Smiley Happy

Year = 
DISTINCT (
    SELECTCOLUMNS (
        CALENDAR ( DATE ( YEAR ( TODAY () ) - 4, 1, 1 ), TODAY () ),
        "Year", YEAR ( [Date] )
    )
)

c1.PNG

 

Regards

That's awesome, thank you

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!