How to count with condition and restart again count

[kongmanee]

I have data in the column like C, NC and N/A

I would like to count continuous of C in this column and it will be restarted again count when appeard only NC.

So, how can I make a table or measure for this.

[AlB]

@kongmanee 

I still do not understand the logic. Why is June 9th not a 1? You have and NC to rest on June 8th

Can you provide the expected result to the input data below?

Date	Value	Column 2
1-Jun-22	C	1
2-Jun-22	NC	0
3-Jun-22	C	1
4-Jun-22	C	1
5-Jun-22	N/A	0
6-Jun-22	C	1
7-Jun-22	N/A	0
8-Jun-22	NC	0
9-Jun-22	C	1
10-Jun-22	NC	0
11-Jun-22	N/A	0
12-Jun-22	C	1
13-Jun-22	NC	0
14-Jun-22	C	1
15-Jun-22	NC	0
16-Jun-22	N/A	0
17-Jun-22	C	1

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[kongmanee]

I am sorry my expectation is below on the desired result column

Date	Value	Column 2	desired result
1-Jun-22	C	1	0
2-Jun-22	C	1	0
3-Jun-22	C	1	0
4-Jun-22	C	1	0
5-Jun-22	N/A	0	0
6-Jun-22	C	1	0
7-Jun-22	N/A	0	0
8-Jun-22	NC	0	0
9-Jun-22	C	1	0
10-Jun-22	C	1	0
11-Jun-22	N/A	0	0
12-Jun-22	C	1	0
13-Jun-22	NC	0	0
14-Jun-22	C	1	1
15-Jun-22	C	1	1
16-Jun-22	N/A	0	0
17-Jun-22	C	1	1

[AlB]

@kongmanee 

This is not the input I posted

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

@kongmanee 

Can you show the final desired result? I do not understand what you mean by "resetting", since all you show is 1, zeros and blanks. Please show a sample table with the expected result. And copy the data in a table here, rather than on a screen cap so that the contents can be copied 

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[kongmanee]

Date	Value	Column 2	desired result
1-Jun-22	C	1	0
2-Jun-22	C	1	0
3-Jun-22	C	1	0
4-Jun-22	C	1	0
5-Jun-22	N/A	0	0
6-Jun-22	C	1	0
7-Jun-22	N/A	0	0
8-Jun-22	NC	0	0
9-Jun-22	C	1	0
10-Jun-22	C	1	0
11-Jun-22	N/A	0	0
12-Jun-22	C	1	0
13-Jun-22	NC	0	0
14-Jun-22	C	1	1
15-Jun-22	C	1	1
16-Jun-22	N/A	0	0
17-Jun-22	C	1	1

[kongmanee]

I am sorry I tried to put the table on the reply screen, but it shows error message. Could you tell me how to put a table on that.

[kongmanee]

[kongmanee]

Date	Value	Column 2	desired result
01-Jun-22	C	1	0
02-Jun-22	C	1	0
03-Jun-22	C	1	0
04-Jun-22	C	1	0
05-Jun-22	N/A	0	0
06-Jun-22	C	1	0
07-Jun-22	N/A	0	0
08-Jun-22	NC	0	0
09-Jun-22	C	1	0
10-Jun-22	C	1	0
11-Jun-22	N/A	0	0
12-Jun-22	C	1	0
13-Jun-22	NC	0	0
14-Jun-22	C	1	1
15-Jun-22	C	1	1
16-Jun-22	N/A	0	0
17-Jun-22	C	1	1

[AlB]

Hi @kongmanee 

Can you show a sample of your data? It needs to have a column to establish order

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[kongmanee]

[AlB]

@kongmanee 

It would help if you actually show the final result in the column you want to create

If you want it in a calculated column in the table you are showing:

NewCol =
VAR date1_ =
    CALCULATE (
        MAX ( Table1[Date] ),
        Table1[Date] < EARLIER ( Table1[Date] ),
        Table1[Value] = "NC",
        ALL ( Table1 )
    )
VAR currentDate_ = Table1[Date]
RETURN
    CALCULATE (
        COUNT ( Table1[Column2] ),
        Table1[Value] = "C",
        Table1[Date] >= date1_,
        Table1[Date] <= currentDate_
    )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[kongmanee]

Thank you for your information.

I alread tried it, but it is not actually that I expected.

I need it by below example.