Next Non Blank Value from Calculated Column

[Anonymous]

Hi all,

First of all, what I'm trying to achieve will not be possible with a fill down method as all operations are being done to calculated columns. So I'd need a DAX calculated column to solve this issue. 

I have the following table that contains an Index that defines a certain contact (we are looking at data for contact # 3 below), IndexDateRank, which orders the entries per contact by date, and Stage, that may or may not have a value for each entry. I want to be able to generate an Outcome column where it calculates what's the next non blank value, as seen below:

Index	IndexDateRank	Stage	Outcome
3	1	6	3
3	2
3	3
3	4
3	5
3	6	3	1
3	7
3	8
3	9
3	10
3	11
3	12
3	13
3	14	1	1
3	15
3	16
3	17
3	18
3	19
3	20	1	5
3	21
3	22
3	23
3	24	5	5
3	25

Does anyone have a solution on how to generate the Outcome column?

Thanks in advance,

Antonio

[v-easonf-msft · Accepted solution]

Hi , @Anonymous 

Try calculated column as below:

Column =
VAR index_ =
    CALCULATE (
        MIN ( [IndexDateRank] ),
        FILTER (
            'Table',
            [IndexDateRank] > EARLIER ( [IndexDateRank] )
                && [Stage] <> BLANK ()
        )
    )
RETURN
    IF (
        [Stage] = BLANK (),
        BLANK (),
        CALCULATE (
            MIN ( 'Table'[Stage] ),
            FILTER ( 'Table', [IndexDateRank] = index_ )
        )
    )

The column result will show as below:

Please check attached pbix file for more details.

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hi Eason,

Wow that's exactly it! You made it look so easy and elegant!

Thank you very much.

Best regards,

Antonio