Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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

11 REPLIES 11
SK87
Helper III
Helper III

@v-ljerr-msft @tamerj1 
I have a similar query in which I need to get the list of latest 10 months in the column based on the dataset I have. I have date column and from there I have got startdate and end date. Based on this I was trying to get below result.

Suppose I have Start Date as 1st Sep'2018 and Last date 30th Sep'2021 from dataset. I want to fetch the data into another calculated field : 30th Sep'21 minus 10 months

Dec'2020
Jan'2021
Feb'2021
March'2021
April'2021......Sep'2021

And I need to use this as Slicer. It should be dynamic so that if next time I get data of Oct'2021 then period would change Jan'2021 - Oct 2021.....

 

Please help me on this.

@SK87 
Please provide more information perhaps some screenshots along with sample data and expected output.

@tamerj1 

I want a slicer in which I can only see latest 10 months option in the dropdown based on date column in the dataset.

 

Date Month EndDate Report
1/31/20191/1/2019
2/28/20192/1/2019
3/31/20193/1/2019
4/30/20194/1/2019
5/31/20195/1/2019
6/30/20196/1/2019
7/31/20197/1/2019
8/31/20198/1/2019
9/30/20199/1/2019
10/31/201910/1/2019
11/30/201911/1/2019
12/31/201912/1/2019
1/31/20201/1/2020
2/29/20202/1/2020
3/31/20203/1/2020
4/30/20204/1/2020
5/31/20205/1/2020
6/30/20206/1/2020
7/31/20207/1/2020
8/31/20208/1/2020
9/30/20209/1/2020
10/31/202010/1/2020
11/30/202011/1/2020
12/31/202012/1/2020
1/31/20211/1/2021
2/28/20212/1/2021
3/31/20213/1/2021
4/30/20214/1/2021
5/31/20215/1/2021
6/30/20216/1/2021
7/31/20217/1/2021
8/31/20218/1/2021
9/30/20219/1/2021
11/30/201811/1/2018
9/30/20189/1/2018
10/31/201810/1/2018
12/31/201812/1/2018

I have enddate from DAX: 

                   MAX(Data[Date Month End])
I will get 30-Sep'2021.
 
Now based on this date I need to show list of Month'Year of past 10 months including this Month. and need to show in slicer.
In Slicer, instead of all data of month'year from start till end I need to see only  10 months dropdown from latest data. 
SK87_1-1657188466522.png

Expected o/p in slicer:

 

Select Month

  Dec'2020

  Jan'2021

  Feb'2021

  March'2021

  April'2021

  May'2021

  June'2021

  July'2021

  August'2021

  Sepetember'2021

 

Hi @SK87 
Please place this measure in the filter pane of the slicer

Filter Measure = 
VAR LastMonth = CALCULATE ( MAX ( Data[Date Month End] ), REMOVEFILTERS ( ) )
VAR FirstMonth = EOMONTH ( LastMonth, -10 )
VAR FilteredMonths = 
    FILTER ( 
        VALUES ( Data[Date Month End] ),
        Data[Date Month End] >= FirstMonth
            && Data[Date Month End] < LastMonth
    )
RETURN
    COUNTROWS ( FilteredMonths )

2.png

@tamerj1 Thanks alot. It works for me. 🙂

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

Anonymous
Not applicable

Hi,

 

I was struggling with the same issue, so this helped me a lot. The only thing I can't get to work is that I would like to have the number of years to "look back" based on a parameter that can be input. The provided solution does not seem to allow to use a input parameter for the calculation of the start date year. 😫

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.