Forum Discussion

rmod32345's avatar
rmod32345
Frequent Visitor
1 year ago
Solved

Return Last Non Blank Measure for All Dates in Table

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...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
Tahreem24
Icon for Super User rankSuper User
1 year ago

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's avatar
rmod32345
Frequent Visitor
1 year ago

Would something similar be possible in a measure?