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 , Thank you for reaching out to the Microsoft Community Forum.
I took your sample data, cleaned it (for example your date format is in different ways, made it one way) and reproduced your scenario, with working solution.
For your reference, I’ve attached the working .pbix file.
If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
Hello v-hashadapu,
I am not seeing any attachment
Thanks,
Raphael
- v-hashadapu
Hi rdehatheba55 , Sorry for the inconvinience, looks like the .pbix didn't get attached properly. Re attaching it now. Please check it and share your thoughts.
Thank you.
