accumulated value with conditions

[dtsjean]

I have a DSR column in which I want to add the values ​​that are not blank, as a counter, if I have a blank value it resets the counter and if it reaches 7 it resets the counter as well.
According to the image below, in red series as I would like the counter to increase.

[Anonymous]

Hi @dtsjean ,

According to your description, you want to sort according to the date column, and then count according to the condition that the DSR column is not empty(If more than 6,count agagin) . Right?

Here are the steps you can follow：

(1)This is my test data:

(2)We can create a calculated column:   “Convert”

Convert =
var _current_date='Sheet2'[DATA]
var _pre_nonblank_date= MAXX( FILTER('Sheet2','Sheet2'[DATA]<_current_date && 'Sheet2'[DSR2]= BLANK()) ,[DATA])
var _count_table=FILTER('Sheet2','Sheet2'[DATA]<=_current_date && 'Sheet2'[DATA]> _pre_nonblank_date )
return
IF(ISBLANK('Sheet2'[DSR2]),0,IF( MOD( COUNTROWS(_count_table),7 ) =0 ,0,1))

(3)Then we create a calculated column to meet your needs:  “Result”

Result = var _current_date='Sheet2'[DATA]
var _pre_date=MAXX(FILTER('Sheet2','Sheet2'[DATA]<=_current_date && 'Sheet2'[Convert] =0),'Sheet2'[DATA])
var _count_table=FILTER('Sheet2','Sheet2'[DATA]<=_current_date && 'Sheet2'[DATA]>_pre_date)
return
IF('Sheet2'[Convert]=0,0, COUNTROWS(_count_table))

(4)Result as follows:

If this method can't meet your requirement, can you provide us with more detailed input and output data to us? We can better understand the problem and help you.

Best Regards,

Liu Yang

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

[dtsjean]

@Anonymous 

I'm setting up a control of days worked, but if you check the image below, not every day I have work records and how much I have i need to calculate between a work record and another 35hr, if there's more than 35hr it counts as dayoff 0 for day off or 1 for worked.

Based on the day off I need to do the following calculation, I need a counter in which if the day off is equal to 1 it adds the counter plus 1, if the day off is equal to 0 it receives zero and only counts again when the day off is 1 again.
and I have one more argument that if the counter reaches 7 automatically it should reset the counter without considering the day off.

thanks a lot for the help so far.

[amitchandak]

@dtsjean , seem like the continuous streak

see if this can help

Continuous streak : https://youtu.be/GdMcwvdwr0o

[dtsjean]

With your tip I was able to make the accumulated value, but when the column DIAS DSR contains the value 0 the accumulated value must return to 0 and if it reaches 7 consecutive it must be with the value 0 as well.
how to get this result?

accumulated =
var a = CALCULATE(
COUNTROWS(Consulta1),
FILTER(Consulta1,
Consulta1[LOGIN] = EARLIER(Consulta1[LOGIN]) && Consulta1[Index]<=EARLIER(Consulta1[Index])))
return
a