Forum Discussion

11097486's avatar
11097486
Helper I
5 years ago
Solved

Find previous blank value in previous rows

Hello, I want to be able to retrieve the value of Date1 of the previous Column1 = empty. 

The result I want is in the 3rd column identified by the red arrow. As you can see, the result can repeat itself since it will search the previous empty colum1 value.

 

Thank you

 

 

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi 11097486 ,

    You can create a calculated column like this:

    Re =
CALCULATE (
    MAX ( 'Table'[Date1] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date1] < EARLIER ( 'Table'[Date1] )
            && 'Table'[Column1] = BLANK ()
    )
)

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

10 Replies

    • 11097486's avatar
      11097486
      Helper I
      5 years ago

      Hello, the data is the first 2 columns of the print screen. Thank you

      • vanessafvg's avatar
        vanessafvg
        Community Champion
        5 years ago

        you need to provide in a usable format, you cannot copy and paste that.  If you need help its preferable you provide it in a usable format.

         

        How do you know the sequence of the data is it date sorted?   what if you have duplicates or is it unique dates only?