Forum Discussion

chitti5's avatar
chitti5
Icon for Helper I rankHelper 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 the...
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]
)
mh2587's avatar
mh2587
Icon for Super User rankSuper 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
    Icon for Helper I rankHelper 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
      Icon for Super User rankSuper User
      2 years ago

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

  • chitti5's avatar
    chitti5
    Icon for Helper I rankHelper 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
      Icon for Super User rankSuper 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]
)