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