Fill down colum based on latest value

[Anonymous]

Hi How to fill the colum based on latest value?

example current month March 2021, PROD value 5, April 2021 - Dec 2021 also 5

Thank

[v-angzheng-msft · Accepted solution]

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.

[v-angzheng-msft · Accepted solution]

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.

[Anonymous · Accepted solution]

This work 🙂

Thank @v-angzheng-msft 

[HarishKM]

@Anonymous  Hey,
You can use below dax formula to fulfil your requirement.

You have to crearte a measure then paste below measure to get the same result.

Sample test measure =

VAR LastNonBlankDate =

CALCULATE (

MAX ( Sheet1[Date] ),

FILTER (

ALL ( Sheet1 ),

Sheet1[Date] <= MAX ( Sheet1[Date] )

&& Sheet1[Budget] <> 0

)

)

RETURN

CALCULATE (

SUM ( Sheet1[Budget] ),

FILTER ( ALL ( Sheet1 ), Sheet1[Date] = LastNonBlankDate )

)
Output will be .

[Anonymous]

@HarishKM 

I tried to replicate your solution, but why my first column not get volue from budget column ?

[HarishKM]

@Anonymous  Hey Mate ,
Do not use it for Coloumn . This will work fine with measure .....

[Jihwan_Kim]

Hi, @Anonymous 

Please check the link below, that is the sample pbix file that I have created.

You can try to use the measure like below.

Filldown based on the last value =

VAR

currentdate =

MAX ( Data[Date] )

VAR

previousvalue =

CALCULATE (

LASTNONBLANKVALUE ( Data[Date], SUM ( Data[Value] ) ),

FILTER ( ALL ( Data[Date] ), Data[Date] < currentdate )

)

RETURN

IF ( NOT ISBLANK ( SUM ( Data[Value] ) ), SUM ( Data[Value] ), previousvalue )

https://www.dropbox.com/s/ahnex94f2rnu5k6/Delion.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[Anonymous]

Hi @Jihwan_Kim 

Thank you so much for your help 🙂

But what I want to achieve is result on power bi table, not visual

Do you have any suggestion for this  ?

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

I think, the column/table in a table view cannot operate fill down behavior.

The best choice I can suggest to transform the table to go to Power Query Editor and use the fill-down feature.

[Ashish_Mathur]

Hi,

In the Query Editor, use the Fill Down feature.

[Angith_Nair]

Hi @Anonymous ,

If the above visual is a table or a matrix, then simply click on the DATE, it will sort the date in ascending or descending order. Descending order will give you the latest value.