Calculated Column Fill Down

Question

Hello,&nbsp;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&nbsp;Sample:&nbsp;Customer-RefDateStatusFixed_StatusA31/12/2015nullNullA01/01/2016HoldHoldA01/01/2016nullHoldA03/01/2016nullHoldA04/01/2016ActiveActiveA05/01/2016nullActiveA06/01/2016HoldHoldA07/01/2016nullHoldB08/01/2016ActiveActiveB09/01/2016nullActiveB10/01/2016HoldHoldB11/01/2016nullHold&nbsp;From some research i think i need to use lastnonblank with a filter based on the customer ref like:&nbsp;https://community.powerbi.com/t5/Desktop/Filling-Data-Gaps-Conditionally/td-p/148745&nbsp;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"&nbsp;im not sure if it complicates the situation in that a customer can have more than one entry on a particular date.&nbsp;I am new to DAX&nbsp;Thank you for any help&nbsp;&nbsp;

mahoneypat · Answer

You can use this column expression to get your result.&nbsp; You may need to change "null" to "", if they are actually null values (vs. the word null).
&nbsp;
&nbsp;
NewStatus&nbsp;=VAR&nbsp;vThisDate&nbsp;=&nbsp;'Status'[Date]RETURN&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LASTNONBLANKVALUE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Status'[Date],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MIN&nbsp;(&nbsp;'Status'[Status]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLEXCEPT&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Status',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Status'[Customer-Ref]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Status'[Date]&nbsp;&lt;=&nbsp;vThisDate,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Status'[Status]&nbsp;&lt;&gt;&nbsp;"null"&nbsp;&nbsp;&nbsp;&nbsp;)
&nbsp;
Regards,
Pat

Forum Discussion

Calculated Column Fill Down

1 Reply

Related content

TMDL - calculated column

Filled map

Direct Lake Model - Calculated Column/Tables

Fill Down in DAX - Calculated column

Calculate last filled columns

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