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 column):

DateTime mm/dd/yyyy hh:mm:ss   Value   Needed Result
6/14/2022 09:00:00 PM100100
6/14/2022 10:00:00 PM107107
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 AM200200
6/15/2022 02:00:00 AM 200
6/15/2022 03:00:00 AM300300

 

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.

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.

2 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community 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.