Forum Discussion

nicolasvc's avatar
nicolasvc
Icon for Helper III rankHelper III
4 years ago
Solved

Fill blank values with last not blank value

I have a table with the value of a product for the first month of the year per store, but I would like to fill the blank values ​​with the last value that is not blank, it is possible to do it using ...
Need Help
Show and Tell
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, nicolasvc 

     

    You can try creating new calculated columns and measures to fill in blank values.

    1. Calculated column

     

    New value = 
CALCULATE (
    MAX ( 'Table'[Value] ),
    FILTER (
        'Table',
        [Store] = EARLIER ( 'Table'[Store] )
            && [Product] = EARLIER ( 'Table'[Product] )
    )
)

     

     

       2. Measure

     

    Measure = 
CALCULATE (
    MAX ( 'Table'[Value] ),
    FILTER (
        ALL('Table'),
        [Store] = MAX ( 'Table'[Store] )
            && [Product] = MAX ( 'Table'[Product] )
    )
)

     

     

    Best Regards,

    Community Support Team _Charlotte

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

     

ERD's avatar
ERD
Icon for Community Champion rankCommunity Champion
4 years ago

nicolasvc 

Do you need a calculated column or a measure?

nicolasvc's avatar
nicolasvc
Icon for Helper III rankHelper III
4 years ago

A calculated column 😞

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    4 years ago

    nicolasvc ,

    There are different options, you can try this one:

    Column =
VAR _s = 'Table'[Store]
VAR _p = 'Table'[Product]
VAR _y = 'Table'[Year]
VAR _m =
    MAXX (
        FILTER ( 'Table', 'Table'[Store] = _s && 'Table'[Product] = _p && 'Table'[Value] <> BLANK () ),
        'Table'[Month]
    )
RETURN
    MAXX (
        FILTER ( 'Table', 'Table'[Store] = _s && 'Table'[Product] = _p && 'Table'[Month] = _m ),
        'Table'[Value]
    )

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