Last Data Value and Count

[sivasrao]

Event Activity	Event	Time	Target 1	Target 2
1	C	1000	1000	0
1	C	1001	1001	0
1	C	1002	1002	3
1	R	1003	1002	0
1	R	1004	1002	0
1	R	1005	1002	0
1	C	1006	1006	0
1	C	1007	1007	2
1	R	1008	1007	0
1	R	1009	1007	0

Target 1: Whenever R comes, the value of the Time column is the previous value of C (like 1002)

Target 2: Count of R's value saved as the value of the previous C column.

Please help with this.

[shrabedi]

For Target 1

Target 1 = 
IF('Table'[Event] = "R",
   CALCULATE(
       MAX('Table'[Time]),
       FILTER('Table', 'Table'[Event] = "C" && 'Table'[Time] < EARLIER('Table'[Time]))
   ),
   'Table'[Time]
)

Regards
Accept as a solution if it works.

[sivasrao]

Thank you for your quick reply.

 Target 2 is the more preferable requirement.

Pls try on this too.

[shrabedi]

Please elaborate on Target 2.

[sivasrao]

The count of R values will be assigned to the previous column of the first R.

In this example, R will start from the 4th column up to the 6th column. The total Count is 3.

The value 3 will assign to the 3rd column.

Same as the 9th and 10th having R, the count is 2. That has been assigned to the 8th column.

I hope you understand the requirement.

[Anonymous]

.

[sivasrao]

Please give proper logic.

[Anonymous]

Sorry @sivasrao , I wanted the notifications on this thread. Wanted to learn how to solve the same question.