Calculated Column Fill Down

RyanStO — Thu, 26 Nov 2020 12:18:10 GMT

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 column

Sample:

Customer-Ref	Date	Status	Fixed_Status
A	31/12/2015	null	Null
A	01/01/2016	Hold	Hold
A	01/01/2016	null	Hold
A	03/01/2016	null	Hold
A	04/01/2016	Active	Active
A	05/01/2016	null	Active
A	06/01/2016	Hold	Hold
A	07/01/2016	null	Hold
B	08/01/2016	Active	Active
B	09/01/2016	null	Active
B	10/01/2016	Hold	Hold
B	11/01/2016	null	Hold

From some research i think i need to use lastnonblank with a filter based on the customer ref like:

https://community.powerbi.com/t5/Desktop/Filling-Data-Gaps-Conditionally/td-p/148745

but this soultion does not completely fit my needs as an example for customer A on the 7th is filled as "Active" rather than "Hold", same for customer B on the 11th filled as "Active" rather than "hold"

im not sure if it complicates the situation in that a customer can have more than one entry on a particular date.

I am new to DAX

Thank you for any help

Re: Calculated Column Fill Down

mahoneypat — Sat, 05 Dec 2020 04:27:33 GMT

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

topic Calculated Column Fill Down in DAX Commands and Tips

Calculated Column Fill Down

Re: Calculated Column Fill Down