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 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.

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
Helper I
5 years ago

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