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 , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[rdehatheba55]

Hello, 

Unfortunately, the solution proposed did not work.

Thanks for trying

@rdehatheba55

[v-hashadapu]

Hi @rdehatheba55 , can you be more specific please about how the solution didn't work and share more details. Thank you.

[rdehatheba55]

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.

[v-hashadapu]

Hi @rdehatheba55 , Hope you are doing well. Kindly let us know if the issue has been resolved or if further assistance is needed. Your input could be helpful to others in the community.

[v-hashadapu]

Hi @rdehatheba55 , I wanted to check with you and see if the provided information was useful. If any of it helped resolve your question, consider marking it as "Accept as Solution" to make it easier for others to find. Let me know if there's anything else I can assist with!

[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.