Calculation of process_time values based on success column using dax calculated columns

[Anonymous]

Hello everyone,

I am new to Power BI and need help to create a new calculated column with DAX. I have this below-mentioned table where status or success columns represent process is finished successfully or not. (1 or F means finished successfully otherwise not), process_time_in_minutes column represents the duration of the process.

id	status	success	process_time_in_minutes
1	F	1	80
2	L	0	70
3	L	0	30
4	F	1	60
5	L	0	50
6	F	1	34

I need a new table like the below one. I want to get total values for a successful process, so if the process has failed then process duration should be kept adding until it gets successful

What I actually want is if the process finished successfully means success = 1 then process time is the same for the next column, otherwise, if it is failed then it should add all previous values till success =1, for first failed process it will be same value as there is no previous failed, but for next ones, it should be kept adding until process finished successfully. 

id	status	success	process_time_in_minutes	updated_process_time_in_minutes
1	F	1	80	80
2	L	0	70	70
3	L	0	30	100
4	F	1	60	160
5	L	0	50	50
6	F	1	34	84

Please help me to write a DAX query for this. Thanks!

[ryan_mayu]

@Anonymous 

you can try to create a column

Column = 
VAR _LAST=maxx(FILTER('Table','Table'[id]<EARLIER('Table'[id])&&'Table'[status]="F"),'Table'[id])
return sumx(FILTER('Table','Table'[id]>_LAST&&'Table'[id]<=EARLIER('Table'[id])),'Table'[process_time_in_minutes])

[Anonymous]

I just tried your query but got an empty column.

[ryan_mayu · Accepted solution]

@Anonymous 

pls try this

Column =

VAR _LAST=MAXX(FILTER(hk_job,hk_job[id]<EARLIER(hk_job[id])&&hk_job[status]="F"),hk_job[id])

return sumx(FILTER(hk_job,hk_job[id]>_LAST&&hk_job[id]<=EARLIER(hk_job[id])),hk_job[process_time])

i have highlighted the difference between my DAX and yours.

pls update your DAX

[Anonymous]

Thank you so much, I just missed renaming that too.

Could you please also explain the query? would be really helpful to understand.

[ryan_mayu]

@Anonymous 

find the last success record, then add up all time from one after last success record until the current record.