Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Populate values in blank rows

Hi All,

 

I have 2 fields in my table as shown below.I have to create a new column which should populate the last non blank value.

i.e: may (data) 50 should populate in a new column for all the months.

 

Table:

 

Required Output:

 

Thanks in Advance.

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

    Hi, Anonymous 

    According to your description, I can clearly understand your requirement, I think you can try my steps:

    1. Create a month column as the number type like this:
    Month=Month([Date])

     

    1. Create a calculated column like this:
    Column =

var _maxmonth=

MAXX(FILTER(ALL('Table'),[Amount]<>BLANK()),[Month])

return

SUMX(FILTER(ALL('Table'),[Month]=_maxmonth),[Amount])

     

    And you can get what you want.

    You can download my test pbix file below

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, Anonymous 

    Please check the below. It is for creating a new column.

     

     

    New Column CC =
    CALCULATE (
    LASTNONBLANKVALUE ( 'Table'[Date], SUM ( 'Table'[Amount] ) ),
    ALL ( 'Table' )
    )
     

    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, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Jihwan_Kim ,

       

      Thanks for quick reply.

       

      I tried to implement the DAX you used but it's throwing few errors.

      Please find the below screenshot for your reference.

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        5 years ago

        Hi, Anonymous 

        Thank you for your feedback.

        Please share your sample pbix file's link here, then I can try to look into it.

        I cannot know how your columns' data type by only seeing the screenshot, for instance, so please share your sample pbix file's link.

        Thanks.

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

    Hi, Anonymous 

    According to your description, I can clearly understand your requirement, I think you can try my steps:

    1. Create a month column as the number type like this:
    Month=Month([Date])

     

    1. Create a calculated column like this:
    Column =

var _maxmonth=

MAXX(FILTER(ALL('Table'),[Amount]<>BLANK()),[Month])

return

SUMX(FILTER(ALL('Table'),[Month]=_maxmonth),[Amount])

     

    And you can get what you want.

    You can download my test pbix file below

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.