Return Last Non Blank Measure for All Dates in Table

[rmod32345]

Hello,

I have a date table connected to a fact table which only evaluates a measure on a monthly basis. I'd like to see the last non blank value of that measure returned for every date that I add to a table visual.

So for example, I'd like to be able to return 1.068M for 8/10, 8/17, 8/24 and then 1.059M for 9/7, 9/14 and so forth. Any ideas on how I'd achieve this?

Thanks

[v-kongfanf-msft · Accepted solution]

Hi @rmod32345 ,

You can try formula like below to create measure:

LastNonBlankMeasure = 
VAR LastNonBlankDate =
    CALCULATE (
        MAX ( 'DateTable'[Date] ),
        FILTER (
            ALL ( 'DateTable' ),
            'DateTable'[Date] <= MAX ( 'DateTable'[Date] )
                && NOT ( ISBLANK ( [total_] ) )
        )
    )
RETURN
    CALCULATE (
        [total_],
        FILTER ( ALL ( 'DateTable' ), 'DateTable'[Date] = LastNonBlankDate )
    )

Best Regards,
Adamk Kong

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

[Tahreem24]

@rmod32345 Try this DAX

NewColumn =

VAR LastNonBlankDate =

    CALCULATE (

        LASTNONBLANK ( 'Table'[Date], 1 ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[Date] <= EARLIER ('Table'[Date] )

                && NOT ( ISBLANK ( 'Table'[Sales] ) )

        )

    )

RETURN

    CALCULATE (

        SUM ( 'Table'[Sales] ),

        FILTER ( ALL ( 'Table' ), 'Table'[Date] = LastNonBlankDate )

    )

[rmod32345]

Would something similar be possible in a measure?