Forum Discussion

pierre415's avatar
pierre415
Icon for Helper I rankHelper I
9 years ago
Solved

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 on...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

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

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

     

    Regards

pierre415's avatar
pierre415
Icon for Helper I rankHelper I
9 years ago

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

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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

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

 

Regards

  • pierre415's avatar
    pierre415
    Icon for Helper I rankHelper I
    9 years ago

    That's awesome, thank you

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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