Fill down colum based on latest value

Hi Anonymous ,

The simplest way is to transform the table by using the fill down feature in the Power Query Editor

Sample data:

Or you can try to create a calculated column

Fill down (column) =
VAR LastNonBlankDate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] <= MAX ( 'Table'[Date] )
                && 'Table'[Tech] <> 0
        )
    )
VAR Tech =
    CALCULATE (
        SUM ( 'Table'[Tech] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = LastNonBlankDate )
    )
RETURN
    IF ( ISBLANK ( 'Table'[Tech] ), Tech, 'Table'[Tech] )

Result:

In addition, you can also to fill down by creating measures

Refer to the above friend's idea, there are 2 method you can try to use.

You can try to use the measure like below:

Method1:

Fill down 1 =
VAR LastNonBlankDate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] <= MAX ( 'Table'[Date] )
                && 'Table'[Tech] <> 0
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Tech] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = LastNonBlankDate )
    )

Method2:

Fill down 2 =
VAR CurrentDate =
    MAX ( 'Table'[Date] )
VAR PreviousValue =
    CALCULATE (
        LASTNONBLANKVALUE ( 'Table'[Date], SUM ( 'Table'[Tech] ) ),
        FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] < CurrentDate )
    )
RETURN
    IF (
        NOT ISBLANK ( SUM ( 'Table'[Tech] ) ),
        SUM ( 'Table'[Tech] ),
        PreviousValue
    )

Result:

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This work 🙂

Thank v-angzheng-msft