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