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.
Super UserHi rdehatheba55 ,
This can be achieved by using the following 2 measures:
Days on Light Duty :=
SUMX(
FILTER(
'Table',
NOT ISBLANK('Table'[Date placed on light duty]) &&
NOT ISBLANK('Table'[Date cleared for duty])
),
DATEDIFF(
'Table'[Date placed on light duty],
'Table'[Date cleared for duty],
DAY
)
)
Days Out of Work :=
SUMX(
FILTER(
'Table',
NOT ISBLANK('Table'[Date removed from duty]) &&
(
NOT ISBLANK('Table'[Date placed on light duty]) ||
NOT ISBLANK('Table'[Date cleared for duty])
)
),
VAR StartDate = 'Table'[Date removed from duty]
VAR EndDate =
IF(
NOT ISBLANK('Table'[Date placed on light duty]),
'Table'[Date placed on light duty],
'Table'[Date cleared for duty]
)
RETURN
DATEDIFF(StartDate, EndDate, DAY)
)
Please make sure to replace 'Table' with your actual table name. In terms of how to utilize each measure based on your request:
- The number of days each spent on light duty
- Use the 'Days on Light Duty' measure with the First Name field in the same visual
- The number of days each spent out of work
- Use the 'Days Out of Work' measure with the First Name field in the same visual
- Total number of days on light duty?
- Use the 'Days on Light Duty' measure in a visual without the First Name field
- Total number of days out of work?
- Use the 'Days Out of Work' measure in a visual without the First Name field
If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.
Thanks,
Samson
Connect with me on LinkedIn
Check out my Blog
Going to the European Microsoft Fabric Community Conference? Check out my Session
Samson,
I converted your formulas using the right table name (See below) and the result is zero.