Complex DAX Calculations with Missing Dates

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

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

Create a calculated table

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.

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!

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.

Hello, 

Unfortunately, the solution proposed did not work.

Thanks for trying

@rdehatheba55

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.