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.

vanessafvg's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
5 years ago

can you provide some dummy data in text form?  

 

 

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.