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

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