Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
The dates follow the formula DD/MM/YYYY
If it's not clear, let me know
Thank you !
Solved! Go to Solution.
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" )
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.
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" )
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.
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |