Forum Discussion

rdehatheba55's avatar
rdehatheba55
Frequent Visitor
1 year ago
Solved

Complex DAX Calculations with Missing Dates

I have the following table list of individuals and their related occurences   Id First Name Incident Date Is this a new incident? Was medical attention sought? Were work restrict...
  • v-hashadapu's avatar
    v-hashadapu
    1 year ago

    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.

techies's avatar
techies
Icon for Super User rankSuper User
1 year ago

Hi rdehatheba55 have tested this and it seems to be working , please try this

 

Create a calculated table

 

LightDutyPeriods =
SELECTCOLUMNS (
    FILTER (
        Incidents,
        NOT ISBLANK ( Incidents[Date placed on light duty] ) &&
        NOT ISBLANK ( Incidents[Date cleared for duty] )
    ),
    "Person", Incidents[First Name],
    "StartDate", Incidents[Date placed on light duty],
    "EndDate", Incidents[Date cleared for duty]
)
 
And the second calculated table as this
 
OutOfWorkPeriods =
SELECTCOLUMNS (
    FILTER (
        Incidents,
        NOT ISBLANK ( Incidents[Date removed from duty] )
            && NOT ISBLANK (
                COALESCE (
                    Incidents[Date placed on light duty],
                    Incidents[Date cleared for duty]
                )
            )
    ),
    "Person", Incidents[First Name],
    "StartDate", Incidents[Date removed from duty],
    "EndDate", COALESCE (
        Incidents[Date placed on light duty],
        Incidents[Date cleared for duty]
    )
)
 
Then create measures 
 
DaysOnLightDuty_Person =
CALCULATE (
    SUMX (
        FILTER (
            LightDutyPeriods,
            LightDutyPeriods[Person] = SELECTEDVALUE ( Incidents[First Name] )
        ),
        DATEDIFF ( LightDutyPeriods[StartDate], LightDutyPeriods[EndDate], DAY ) + 1
    )
)
 
DaysOutOfWork_Person =
CALCULATE (
    SUMX (
        FILTER (
            OutOfWorkPeriods,
            OutOfWorkPeriods[person] = SELECTEDVALUE ( Incidents[First Name] )
        ),
        DATEDIFF ( OutOfWorkPeriods[StartDate], OutOfWorkPeriods[EndDate], DAY ) + 1
    )
)
 
For the above 2 measures, you can test them using the first name as a slicer from the incidents table, and checking for each
 
Next measures for all
 
Total_DaysOnLightDuty =
SUMX (
    LightDutyPeriods,
    DATEDIFF ( LightDutyPeriods[StartDate], LightDutyPeriods[EndDate], DAY ) + 1
)
 
Total_DaysOutOfWork =
SUMX (
    OutOfWorkPeriods,
    DATEDIFF ( OutOfWorkPeriods[StartDate], OutOfWorkPeriods[EndDate], DAY ) + 1
)