Complex DAX Calculations with Missing Dates

Hi rdehatheba55 , Thanks for sharing the detailed scenario very helpful.

While working with the provided data, I noticed some inconsistencies in the date formats and a few missing values in key columns like "Date cleared for duty" and "Date removed from duty." These issues make it difficult to calculate durations accurately, especially in complex cases like Rebeccah's with multiple transitions.

To get precise results, it would help if.

• All date fields use a consistent format (e.g., MM/DD/YYYY),
• Missing start or end dates for light duty or time out of work are clarified or completed,
• Repeated transitions are logged clearly as separate rows.

Once that’s addressed, the DAX logic can be adjusted to reflect each scenario accurately. Happy to help with that if you can share a cleaned version.