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() ) 
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Essentially, I just need a cumulative total (in order of date)

    • MattAllington's avatar
      MattAllington
      Community Champion
      6 years ago

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