Replace Null with in Date field with another Date

[Anonymous]

Hi,

I have a column with job completion dates in it and I'm trying to fill in the blanks by using data from another data source.

The link between the two data sets is the job number. 

This is what I have so far. But I'm struggling to add some extra criteria (where clause) to narrow down what dates should be pulled back.

New Comp Dates = IF('Target Data'[repair_status] = 'Closed' && 'Target Data'[date_comp] = BLANK() , 'Stage History'[date_Comp] WHERE 'Stage History'[Stage_Status] = 'TBSD', 'Target Data'[date_comp])

How would I go about making sure that is pulling the right date over (right date per job number) as well as the date associated with the comp status change?

Thanks in advance

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

we can create a calculated column using following DAX:

New Comp Dates =
IF (
    'Target Data'[repair_status] = "Closed"
        && 'Target Data'[date_comp] = BLANK (),
    CALCULATE (
        MAX ( 'Stage History'[date_Comp] ),
        FILTER (
            'Stage History',
            'Stage History'[job number] = EARLIER ( 'Target Data'[job number] )
                && 'Stage History'[Stage_Status] = "TBSD"
        )
    ),
    [date_comp]
)

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.

Best regards,

[amitchandak]

one way is to Get The date from the second table to the first table. As a column

New date =maxx(filter(table2,table2[job]=table[job]),required_date)

Then using isblank and if you can replace the value.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[parry2k]

@Anonymous how your tables relationship looks like? Many to One?? Which side is Many and which side is One