Complex DAX Calculations with Missing Dates

[rdehatheba55]

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 restrictions put into place?	Date placed on light duty	Date reported	Date cleared for duty	Date removed from duty
8	Gloria	5/26/2024	Yes	No			5/26/2024	5/31/2024	5/26/2024
9	Gloria	2/20/2025	Yes	Yes	Yes, the employee was removed from duty		2/20/2025		2/20/2025
10	Gloria	2/20/2025	No		Yes, the employee was given limitations	4/1/2025
11	Gloria	2/20/2025	No		No work restrictions			5/12/2025
12	Rebeccah	4/1/2025	Yes	Yes	Yes, the employee was removed from duty		4/1/2025		4/4/2025
13	Rebeccah	4/1/2025	No		Yes, the employee was given limitations	4/16/2025
14	Rebeccah	4/1/2025	No		Yes, the employee was removed from duty				4/30/2025
15	Rebeccah	4/1/2025	No		Yes, the employee was given limitations	5/21/2025
16	Diana	6/17/2025	Yes	Yes	No work restrictions		6/17/2025	6/17/2025

Scenario I

Gloria has an accident on 5/26/2024.

• She is removed from work 5/26/2024
• She is back to work on 5/31/2024

Scenario II

Diana has an accident on 6/17/2025.

• She does not get removed from work.
• On 6/17/2025, she is cleared up for duty.

Scenario III

Gloria has an accident on 2/20/2025.

• She is removed from work on 2/20/2025.
• On 4/1/2025, she is admitted back to work on light duty
• She is eventually cleared for duty on 5/12/2025.

Scenario IV

Rebeccah has an accident on 4/1/2025.

• She gets removed from work initially on 4/4/2025.
• On 4/16/2025, she returns to work and is placed on light duty (Restriction).
• On 4/30/2025, she is removed again from work. After some additional time out,
• She returns to light duty on light duty on 5/21/2025

Problem

Using DAX, compute the following:

• The number of days each spent on light duty
• The number of days each spent out of work
• Total number of days on light duty?
• Total number of days out of work?

I would appreciste any assistance with the above.

Thanks,

[v-hashadapu]

Hi @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.

[rdehatheba55]

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.

[techies]

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

)

[SamsonTruong]

Hi @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

[rdehatheba55]

Samson,

I hate to disappoint but I am getting no yield from the calculated tables and therefore cannot move any further. I not sure what I am doing wrong. 

[rdehatheba55]

Samson,

I converted your formulas using the right table name (See below) and the result is zero.

Days on Light Duty =

SUMX(

    FILTER(

        Accidents,

        NOT ISBLANK(Accidents[Date placed on light duty]) &&

        NOT ISBLANK(Accidents[Date cleared for duty])

    ),

    DATEDIFF(

                Accidents[Date placed on light duty],

        Accidents[Date cleared for duty],

        DAY

    )

)

I did the same thing with the Days Out of Work formula proposed and it yields no result. See screenshot below.