Add + 1 to previous row

[Anonymous]

Hi All

I was able to create the "Cycle" column using this formular in MS Excel: =IF(A2=A1;IF(B2 = "Reboot";C1+1;C1+0);0). Basically, if the name in the current row matches the name in the previous row, the proceed but if not then 0. The formular proceeds to add 1 to subsequent tasks when TaskName  = Reboot. It's quite easy in Excel, but can it be done in Power BI? 

Name	TaskName	Cycle
Mtsweni	Enrol	0
Mtsweni	Invite	0
Mtsweni	Reboot	1
Mtsweni	Consult	1
Mtsweni	Reboot	2
Mtsweni	Record BC	2
Mtsweni	Record BC	2
Mtsweni	Farewell	2
Mtsweni	Consult	2
Mtsweni	Reboot	3
Mtsweni	Consult	3
Mtsweni	Consult	3
Thabiso	Enrol	0
Thabiso	Invite	0
Thabiso	Sent Reminder	0
Thabiso	Reboot	1
Thabiso	Send Materials	1
Thabiso	Reboot	2
Thabiso	Record BC	2
Thabiso	Record BC	2
Thabiso	Farewell	2
Thabiso	Consult	2
Thabiso	Reboot	3
Thabiso	Consult	3
Thabiso	Consult	3
Thabiso	Comprehensive Diabetes Tests	3
Thabiso	Send Materials	3
Paul	Enrol	0
Paul	Invite	0
Paul	Send Materials	0
Paul	Welcome	0
Paul	Reboot	1
Paul	Record BC	1
Paul	Reboot	2
Paul	Record BC	2
Paul	Record BC	2
Paul	Farewell	2
Paul	Consult	2
Paul	Reboot	3
Paul	Consult	3
Paul	Consult	3

[v-jayw-msft · Accepted solution]

Hi @thabz_mogale,

Please check following calculated column as below and see if it meet your exception:

Cycle =

CALCULATE (

    COUNTROWS ( 'Table' ),

    FILTER (

        'Table',

        'Table'[Name] = EARLIER ( 'Table'[Name] )

            && 'Table'[Index] <= EARLIER ( 'Table'[Index] )

            && 'Table'[TaskName] = "Reboot"

    )

) + 0

Result would be shown as below:

Best Regards,

Jay

Community Support Team _ Jay Wang

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