Dax or power query

[felipesaw]

Hello I need help to make this column "HELP"

If the value from "Value" to positive, just repeat that value in HELP.

If the value from "Value" to negative:

      -If the previous value value is positive, then I put the negative value in HELP.

      -If the last two values of negative values, i add both and put in HELP.

             - If the next value for positive discount this negative value or sum of negative values of HELP

Data	Value	HELP
01/jan/21	100	100
01/fev/21	200	200
01/mar/21	-300	-300
01/abr/21	500	200
01/mai/21	100	100
01/jun/21	-200	-200
01/jul/21	-300	-500
01/ago/21	1000	500
01/set/21	100	100

[v-xiaotang · Accepted solution]

Hi @felipesaw 

Thanks for reaching out to us.

Just create 2 measures,

count = 
var _pre=MAXX(FILTER(ALL('Table'),'Table'[Data]<MIN('Table'[Data]) && 'Table'[Value]>=0),[Data])
return CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Data]>_pre && 'Table'[Data]<MIN('Table'[Data])))

Test = 
var _start=IF([count]<>BLANK(), MAXX(FILTER(ALL('Table'),'Table'[Data]<MIN('Table'[Data])&&ISBLANK([count])),[Data]))
var _sum=SUMX(FILTER(ALL('Table'),'Table'[Data]<=MIN('Table'[Data]) &&'Table'[Data]>=_start ),[Value])
return IF(ISBLANK([count]),MIN('Table'[Value]),_sum)

result

Best Regards,

Community Support Team _Tang

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