Indexing a Column for Cycles by Day

[jstein91694]

Hello!

I have a column of data that is either a 0 or a 1, which is basically showing on/off. I would like to index the 1's for each day to show the number of cycles per day.

For example, a column of this [0,0,0,1,0,0,0,0,1,0,0,1]

Would become [0,0,0,1,1,1,1,1,2,2,2,3].

When the date changes to the next day, the index would reset to 0.

Thanks for the help!

[Anonymous · Accepted solution]

Hi  @jstein91694 ,

First create an index column:

Go to query editor>add column>index column;

Then create a calculated column as below:

Column 2 = 
var _date=CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1))
Return
IF('Table'[Date]<>_date,0,IF('Table'[Column]<>0,RANKX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])&&'Table'[Column]=1),'Table'[Index],,ASC,Dense),RANKX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])&&'Table'[Column]=1),'Table'[Index],,ASC,Dense)-1))

And you will see;

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@jstein91694 , you need to have index or incremental column or date and post that you can have formula like

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

sumx(filter(Table,[index] <=earlier([index])),[column])

[az38]

Hi @jstein91694 

if your data is ordered with DateTime column, try create a new column

Column = CALCULATE(SUM('Table'[Column1]), FILTER(ALLEXCEPT('Table', 'Table'[DateTime].[Date]), 'Table'[DateTime] <= EARLIER('Table'[DateTime])))

[lbendlin]

Just do a running total sum of the column per day. Free index!