Forum Discussion
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 wall on this one. any suggestions?
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() ) )
6 Replies
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 )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
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() ) )
You could use the technique I wrote about here. https://exceleratorbi.com.au/replace-guids-with-a-surrogate-key-for-better-performance/
I'm not sure it is a good idea - depends what you are trying to do.
I recommend you create a calendar table.
https://exceleratorbi.com.au/power-pivot-calendar-tables/
join on the date column you have already. Use the calendar date column in your visual, not the date in your current table. Then you can write a measure
=CALCULATE(sum(table[value column]),all(calendar),filter(all(calendar[date]),calendar[date] <= max(calendar[date])))
