Help with dax nonblank

[Anonymous]

Hi team,

I need help with a dax calculated column. I have a datetime column and value column. I need a dax calulation to take the previous nonblank value. Here is the needed result (the right column):

DateTime mm/dd/yyyy hh:mm:ss	Value	Needed Result
6/14/2022 09:00:00 PM	100	100
6/14/2022 10:00:00 PM	107	107
6/14/2022 11:00:00 PM		107
6/15/2022 00:00:00 AM		107
6/15/2022 01:00:00 AM		107
6/15/2022 01:00:00 AM	200	200
6/15/2022 02:00:00 AM		200
6/15/2022 03:00:00 AM	300	300

When there is empty rows, the calculation should take the previous nonblank row value and copy it,  until there is data in the value column. 

Thank you.

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous,

DAX to do that.

Column =
VAR a =
    MAXX (
        TOPN (
            1,
            FILTER (
                'Table',
                [DateTime mm/dd/yyyy hh:mm:ss]
                    < EARLIER ( 'Table'[DateTime mm/dd/yyyy hh:mm:ss] )
                    && NOT ( ISBLANK ( [Value] ) )
            ),
            [DateTime mm/dd/yyyy hh:mm:ss], DESC
        ),
        [Value]
    )
RETURN
    IF ( [Value] = BLANK (), a, [Value] )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SpartaBI]

@Anonymous just to be sure, you don't want to do it in Power Query? Just 2 steps

Showcase Report – Contoso By SpartaBI