Forum Discussion

chitti5's avatar
chitti5
Helper I
2 years ago
Solved

Completed Project - Missing Dates

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.

 

 

Need Help
  • danextian's avatar
    danextian
    2 years ago

    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]
)

5 Replies

  • mh2587's avatar
    mh2587
    Super User
    2 years ago
    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's avatar
      chitti5
      Helper I
      2 years ago

      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's avatar
        danextian
        Super User
        2 years ago

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

    • chitti5's avatar
      chitti5
      Helper I
      2 years ago

      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's avatar
        danextian
        Super User
        2 years ago

        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]
)