Forum Discussion

EpicTriffid's avatar
EpicTriffid
Helper IV
4 years ago
Solved

Dynamic index in custom order

Hi all,   Bit of an interesting one. I have a table below, with a range of years, but I need an index in the order shown below:   Year Index 2016 7 2017 6 2018 5 2019 4 ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    EpicTriffid , you can create a Calculated Column with this DAX formula below. I have named the table YearIndex in my example.

    Index = 
    IF(
        'YearIndex'[Year] <= YEAR(now()), YEAR(now()) - 'YearIndex'[Year] + 1
        ,'YearIndex'[Year] - YEAR(now()) + YEAR(now()) - MIN('YearIndex'[Year]) + 1
    )

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

EpicTriffid , you can create a Calculated Column with this DAX formula below. I have named the table YearIndex in my example.

Index = 
    IF(
        'YearIndex'[Year] <= YEAR(now()), YEAR(now()) - 'YearIndex'[Year] + 1
        ,'YearIndex'[Year] - YEAR(now()) + YEAR(now()) - MIN('YearIndex'[Year]) + 1
    )

 

 

EpicTriffid's avatar
EpicTriffid
Helper IV
4 years ago

Just an absolute legend! So simple!