Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with dax nonblank

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 colu...
Need Help
Show and Tell
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

v-chenwuz-msft's avatar
v-chenwuz-msft
Icon for Community Support rankCommunity Support
4 years ago

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.