Forum Discussion

EpicTriffid's avatar
EpicTriffid
Icon for Helper IV rankHelper 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
20176
20185
20194
20203
20212
20221
20238
20249
202510
202611

 

 

Now, I can isolate the current , earliest and last year in the the table, but i'm lost of how to generate the numbers in this particular order. Basically, it has to be that the current year (in this case 2022) is always number one, then it increments up each year it goes back in time. Once it reaches the earliest year, it then continues the incrementing values until it hits the last year. 

 

Any thoughts?

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
    )

     

     

5 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I assume you want to create this by a measure.

    Please check the below picture and the attached pbix file.

     

     

     

    Conditional index measure: =
VAR _previousperiodrankingtable =
    ADDCOLUMNS (
        ALL ( Data ),
        "@previousperiodrank",
            CALCULATE (
                RANKX (
                    FILTER ( ALL ( Data ), Data[Year] <= YEAR ( TODAY () ) ),
                    CALCULATE ( MAX ( Data[Year] ) ),
                    ,
                    DESC
                )
            )
    )
VAR _lastnumberinranking =
    MAXX ( _previousperiodrankingtable, [@previousperiodrank] )
RETURN
    IF (
        HASONEVALUE ( Data[Year] ),
        IF (
            MAX ( Data[Year] ) <= YEAR ( TODAY () ),
            RANKX (
                FILTER ( ALL ( Data ), Data[Year] <= YEAR ( TODAY () ) ),
                CALCULATE ( MAX ( Data[Year] ) ),
                ,
                DESC
            ),
            RANKX (
                FILTER ( ALL ( Data ), Data[Year] > YEAR ( TODAY () ) ),
                CALCULATE ( MAX ( Data[Year] ) ),
                ,
                ASC
            ) + _lastnumberinranking
        )
    )

     

  • 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
    )

     

     

    • tackytechtom's avatar
      tackytechtom
      Icon for Most Valuable Professional rankMost Valuable Professional
      4 years ago

      Hi Anonymous ,

       

      I like this one! I knew, there was an easier way than my initial solution 🙂

       

      I translated your code to M:

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNFOK1QEzzGEMCxjDEsowMoAxDGEMIxjDGMYwgTFMYQygybEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Index", each if [Year] <= Date.Year(Date.From(DateTime.LocalNow())) then Date.Year(Date.From(DateTime.LocalNow()))- [Year] + 1 else [Year] - List.Min(#"Changed Type"[Year]) + 1)
in
    #"Added Custom"

       

      /Tom
      https://www.tackytech.blog/
      https://www.instagram.com/tackytechtom/

  • tackytechtom's avatar
    tackytechtom
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Hi EpicTriffid ,

     

    Here a solution in Power Query:

     

     

     

    Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNFOK1QEzzGEMCxjDEsowMoAxDGEMIxjDGMYwgTFMYQygybEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Year", Order.Descending}}),
    #"Filtered Rows 1" = Table.SelectRows(#"Sorted Rows", each [Year] <= Date.Year(Date.From(DateTime.LocalNow()))),
    #"Added Index 1" = Table.AddIndexColumn(#"Filtered Rows 1", "Index", 1, 1, Int64.Type),
    #"Added Custom 1" = Table.AddColumn(#"Added Index 1", "MaxFromPrevYears", each List.Max(#"Added Index 1"[Index])),
    #"Filtered Rows 2" = Table.SelectRows(#"Sorted Rows", each [Year] > Date.Year(Date.From(DateTime.LocalNow()))),
    #"Sorted Rows1" = Table.Sort(#"Filtered Rows 2",{{"Year", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows1", "Index", 1, 1, Int64.Type),
    #"Appended Query" = Table.Combine({#"Added Custom 1", #"Added Index"}),
    #"Filled Down" = Table.FillDown(#"Appended Query",{"MaxFromPrevYears"}),
    #"Added Custom 2" = Table.AddColumn(#"Filled Down", "IndexFinal", each if [Year] > Date.Year(Date.From(DateTime.LocalNow())) then [Index] + [MaxFromPrevYears] else [Index]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom 2",{"Index", "MaxFromPrevYears"}),
    #"Sorted Rows2" = Table.Sort(#"Removed Columns",{{"Year", Order.Ascending}})
in
    #"Sorted Rows2"

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/