Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Add column, calculated index

I need a way to add a calculated index column similar to this example      * does not include blank * orders the dates in ascending order * no values duplicated.    I'm really hitting a ...
Need Help
  • az38's avatar
    az38
    6 years ago

    Anonymous 

    below sentence should work fine

    Index = 
var CurrentDate = Table[Date]

RETURN
IF(
 ISBLANK(CurrentDate), BLANK(),
 CALCULATE(COUNTROWS(Table), Table[Date] <= CurrentDate, Table[Date] <> BLANK() ) 
)
az38's avatar
az38
Community Champion
6 years ago

Anonymous 

how do you want to resolve tie situation like with 15/02/2019?

In the common case try smth like

Index = 
var CurrentDate = Table[Date]
var valuesWithCurrentDate = CALCULATE(COUNTROWS(Table), Table[Date] = valuesWithCurrentDate )
var addValue = IF(valuesWithCurrentDate < 2, 1, 
               RANKX(FILTER(Table, Table[Date] = CurrentDate) , Table[Second Rank Field]) )

RETURN
IF(
 ISBLANK(CurrentDate), BLANK(),
 CALCULATE(COUNTROWS(Table), Table[Date] < CurrentDate, Table[Date] <> BLANK() ) + addValue 
)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    az38 duplicate dates don't make a huge difference providing they are counted, for example, something similar to the following would work fine. 

     

    1/1/2020        1

    1/2/2020        3

    1/2/2020        3

    1/3/2020        4

    1/4/2020        7

    1/4/2020        7

    1/4/2020        7

    • az38's avatar
      az38
      Community Champion
      6 years ago

      Anonymous 

      below sentence should work fine

      Index = 
var CurrentDate = Table[Date]

RETURN
IF(
 ISBLANK(CurrentDate), BLANK(),
 CALCULATE(COUNTROWS(Table), Table[Date] <= CurrentDate, Table[Date] <> BLANK() ) 
)