Forum Discussion

sivasrao's avatar
sivasrao
Helper III
3 years ago

Last Data Value and Count

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 ...
Need Help
Tips and Tricks
shrabedi's avatar
shrabedi
Frequent Visitor
3 years ago

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's avatar
    sivasrao
    Helper III
    3 years ago

    Thank you for your quick reply.

     Target 2 is the more preferable requirement.

    Pls try on this too.

    • shrabedi's avatar
      shrabedi
      Frequent Visitor
      3 years ago

      Please elaborate on Target 2.

      • sivasrao's avatar
        sivasrao
        Helper III
        3 years ago

        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.