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 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

 

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.

3 Replies

  • 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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.