Forum Discussion

11097486's avatar
11097486
Icon for Helper I rankHelper 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 it...
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.

11097486's avatar
11097486
Icon for Helper I rankHelper I
5 years ago

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

vanessafvg's avatar
vanessafvg
Icon for Community Champion rankCommunity 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?

 

 

  • 11097486's avatar
    11097486
    Icon for Helper I rankHelper I
    5 years ago

    Hello, yes it is date sorted and there won't be any duplicates. I cannot insert files. Creating the table should take 2 minutes... Thank you

     

     

     

     

    • v-yingjl's avatar
      v-yingjl
      Icon for Community Support rankCommunity Support
      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.

      • 11097486's avatar
        11097486
        Icon for Helper I rankHelper I
        5 years ago

        It works perfectly! Thank you!