Forum Discussion
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.
Community SupportHi rdehatheba55 , can you be more specific please about how the solution didn't work and share more details. Thank you.
I am unable to justifythe following:
- 335 days of light duty work
- Negative value for days out of work
Additionally, how do you solve the problem Rebeccah (Scenario IV for instance)
Thank you for trying but I am afraid I am still left without a solution.
- v-hashadapu
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.