Forum Discussion

RyanStO's avatar
RyanStO
New Member
5 years ago

Calculated Column Fill Down

Hello,   I am looking to fil down a column based on a Customer Reference column in order to fill null entries with the last non blank entry for that customer which would look like the fixed_status ...
mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

You can use this column expression to get your result.  You may need to change "null" to "", if they are actually null values (vs. the word null).

 

 

NewStatus =
VAR vThisDate = 'Status'[Date]
RETURN
    CALCULATE (
        LASTNONBLANKVALUE (
            'Status'[Date],
            MIN ( 'Status'[Status] )
        ),
        ALLEXCEPT (
            'Status',
            'Status'[Customer-Ref]
        ),
        'Status'[Date] <= vThisDate,
        'Status'[Status] <> "null"
    )

 

Regards,

Pat