Completed Project - Missing Dates

[chitti5]

Hi,

I have the data from 2017 and it has like 700 projects completed with missing project completion dates. I can't add the completion date manually for all 700 projects and I don't even know their exact date of completion. I need a DAX measure or any calculated column to apply so that I can add the missing dates with any random dates to work on my power bi report. Please help.

Here is the screenshot of the sample data.

[mh2587]

Random Completion Date = // Try this one 
IF(ISBLANK([Completion Date]),
    DATE(2017, 1, 1) + RANDBETWEEN(0, 365 * (2024 - 2017)),  -- Random date between 2017 and 2024
    DATE(
        2017 + RANDBETWEEN(0, 7),  -- Random year between 2017 and 2024
        RANDBETWEEN(1, 12),         -- Random month
        RANDBETWEEN(1, 28)          -- Random day
    )
)

[chitti5]

@danextian @mh2587 I tried both your formulas and it worked. The only issue I have is all the random completed dates are showing even before it's start date. Please suggest if I should add anything to the existing formula to show the random completed dates after the start date.

[danextian]

You can add a random number to an existing date if status is completed and date is blank

IF (
    'table'[status] = "completed"
        && ISBLANK ( 'table'[end date] ),
    'table'[end date] + RANDBETWEEN ( 7, 365 ),
    'table'[end date]
)

[chitti5]

@mh2587 Hi, Thank you for the reply. There is another column in the table with "Status" of the project. The Completion date column has many blanks which also includes blanks for the Status "Active" projects and I want that to remain blank itself since they are currently active in system. So is there any DAX measure which can show the missing dates of the completed dates column for only "Completed" status and not for other status.

Thank you!

[danextian]

You can add a condition to @mh2587 's formula. IF ('table'[status] = "Completed", <formula>)