Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |