How to auto increment the values if other column is not null?

[Anonymous]

I have 1 column as open date, i need  to add a new column/measure in power BI to have the incremented values as shown below

Open Date	New Column
7/24/13 5:23 PM	1
7/24/13 5:23 PM	2
7/24/13 5:23 PM	3
7/24/13 5:23 PM	4
7/24/13 5:23 PM	5
9/26/13 6:00 PM	6
9/26/13 6:00 PM	7
null/blank
null/blank
null/blank
9/26/13 6:00 PM	8
9/26/13 6:00 PM	9
9/26/13 6:00 PM	10
9/26/13 6:00 PM	11
9/26/13 6:00 PM	12
11/1/13 9:00 PM	13
11/1/13 9:00 PM	14
6/11/14 10:00 AM	15
6/11/14 10:00 AM	16
6/11/14 10:00 AM	17
6/11/14 10:00 AM	18
6/11/14 10:00 AM	19

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on my research, you may click 'Edit Query', go to Query Editor, go to 'Add Column' ribbon, click 'Index Column', make 'from 1 ' selected, close and apply changes.

Then you may create a calculated column as follows.

Skip Null Index = 
IF(
    ISBLANK([Open Date]),
    BLANK(),
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            'Table'[Index]<=EARLIER([Index]) 
            && NOT(ISBLANK([Open Date]))
        )
    ) 
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank You @v-alq-msft . It worked.

Regards,

Shalini

[amitchandak]

Go to edit the query and add the index column. After that create rank and use the index column to break ties.

https://community.powerbi.com/t5/Desktop/Rank-distinct-values-for-ties/td-p/194301