Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Fill down colum based on latest value

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

 

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    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.

10 Replies

  • Angith_Nair's avatar
    Angith_Nair
    Continued Contributor
    5 years ago

    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.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    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 )
     
     

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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  ?

       

       

       

       

  • HarishKM's avatar
    HarishKM
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      HarishKM 

       

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

       

       

      • HarishKM's avatar
        HarishKM
        Super User
        5 years ago

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

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    5 years ago

    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.