Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lucaslopesp
Regular Visitor

How to create a DAX fuction to check date projects clash

I have a database where I can find the following informations: Resource, project, start date and end date. I would like to create a function that is able to check if the resource's projects dates clash. The outcome would be like the Status column bellow:

lucaslopesp_0-1680095007348.png

 

The dates follow the formula DD/MM/YYYY

 

If it's not clear, let me know

 

Thank you !

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @lucaslopesp ,

 

We can rely on overlapping days to judge whether there is a conflict.

You can create a measure as 

Status =
VAR _table =
    SUMMARIZE (
        ALL ( 'Table' ),
        [Resource],
        'Table'[Project],
        'Table'[Start],
        'Table'[End],
        "DateDiff", DATEDIFF ( [Start], [End], DAY )
    )
VAR _days1 =
    SUMX ( FILTER ( _table, [Resource] = MAX ( 'Table'[Resource] ) ), [DateDiff] )
VAR _days2 =
    DATEDIFF (
        CALCULATE (
            MIN ( 'Table'[Start] ),
            FILTER ( ALLSELECTED ( 'Table' ), [Resource] = MAX ( 'Table'[Resource] ) )
        ),
        CALCULATE (
            MAX ( 'Table'[End] ),
            FILTER ( ALLSELECTED ( 'Table' ), [Resource] = MAX ( 'Table'[Resource] ) )
        ),
        DAY
    )
RETURN
    IF ( _days1 > _days2, "Check", "OK" )

 

vstephenmsft_0-1680240946445.png

                                                                                                                                                         

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

View solution in original post

5 REPLIES 5
v-stephen-msft
Community Support
Community Support

Hi @lucaslopesp ,

 

We can rely on overlapping days to judge whether there is a conflict.

You can create a measure as 

Status =
VAR _table =
    SUMMARIZE (
        ALL ( 'Table' ),
        [Resource],
        'Table'[Project],
        'Table'[Start],
        'Table'[End],
        "DateDiff", DATEDIFF ( [Start], [End], DAY )
    )
VAR _days1 =
    SUMX ( FILTER ( _table, [Resource] = MAX ( 'Table'[Resource] ) ), [DateDiff] )
VAR _days2 =
    DATEDIFF (
        CALCULATE (
            MIN ( 'Table'[Start] ),
            FILTER ( ALLSELECTED ( 'Table' ), [Resource] = MAX ( 'Table'[Resource] ) )
        ),
        CALCULATE (
            MAX ( 'Table'[End] ),
            FILTER ( ALLSELECTED ( 'Table' ), [Resource] = MAX ( 'Table'[Resource] ) )
        ),
        DAY
    )
RETURN
    IF ( _days1 > _days2, "Check", "OK" )

 

vstephenmsft_0-1680240946445.png

                                                                                                                                                         

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

Wus
Regular Visitor

Greetings! I don't understand what you need to compare, for example, what's the difference between "OK" and "Check". Could you explain a bit more? Thank you!

Sure!

 

In this situation, "OK" means the Patrick's projects dates don't clash.

"Check" would mean John's projects dates clash.

 

I hope I became it clear.

 

Thanks

Thank you for the information. However, I understand what you are explaining, but I am not clear on the basis for identifying which dates match and which ones do not. I am specifically thinking about the DAX formula that needs to be created. In the example table provided, I am unable to determine the criteria for determining whether it is "OK" or "Check". I apologize for so many questions, but I am still confused. Thank you!

Hello,

 

The criteria for determining "OK" is the projects dates don't clash. Lets take Patrick's projects exeample.

The projecet B range date is before project D range date. It means Patrick can work full time in project B and then start project D activities. 

 

Now let's think about John situation, Project C dates run while Project A dates aren't over yet. So I need to "check" and define a new resource to deal with one of them.

 

If something isn't clear yet, please let me know.

 

Thanks.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.