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.

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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi,

     

    My table is getting created in PowerBI model itself and I dont have the same coming from the query. It is essentially a bigger challenge that I am trying to solve with this exercise- I will be tabulating this and assigning a variable to this table and use it in my next calculation, hence wanted to use DAX for this, please help!

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

      I tried with CALCULATE but it doesn't work with string. I unfortunately wasn't able to find a solution...