Finding common part of Dates dataset

[MatCienk]

Hello,

I am looking for a solution for showing the collision of dates for the resource usage.

So I have a table with start date column, finish date column and a resource.

I would like to show somehow that the same resource has common set of dates used (in different row of table).

I am working on a Driect Query Table which in my opinion makes it harder.

I was tring to list the dates between start and finish and then somehow compare the rows based on single days and resource but can't make that using Direct Query.

Example:

Resource A	1-1-2010	30-1-2010
Resource B	1-1-2010	30-1-2010
Resource C	1-2-2010	20-2-2010
Resource A	10-1-2010	15-1-2010	Collision!
Resource B	1-2-2010	20-2-2010
Resource A	1-2-2010	20-2-2010

[BA_Pete · Accepted solution]

Hi @MatCienk ,

The quickest way I can think to do this within a Direct Query model would be to create a count-over-time measure against your table, using a disconnected calendar table, something like this:

_overlapCount = 
VAR __cDate = MAX(calendar[date])
RETURN
CALCULATE(
    COUNT(aTable[resource]),
    KEEPFILTERS(__cDate >= aTable[startDate]),
    KEEPFILTERS(__cDate <= aTable[finishDate])
)

You can then put aTable[resource], calendar[date], and [_overlapCount] into a table and filter the table on [_overlapCount] > 1, like this:

Pete

[MatCienk]

I am not an IT person so sorry if I get something wrong but here is what I've got.

I couldn't get it work using disconnected calendar but I have some results useing:

VAR __cDate = MAX(Zadania[Finish])

It seems to catch some collision of dates, but not all of them:

1st example:

2nd example:

It catches the collision if the dates are equal, but not if they are contained one within the other.

Would You suggest any fix for that?

[BA_Pete]

Hi @MatCienk ,

When you say you couldn't get it to work without a disconnected calendar table, what do you mean? Do you have a calendar table in your model? My answer assumed that you did.

You can add a calendar to your model very easily by going to the Modelling tab> New Table and typing this in the formula bar:

calendar = CALENDARAUTO()

It's not the most efficient method of creating a model calendar, but it will get you started.

Once this is done, my original measure should work as expected (Note: you may need to change [date] in my measure to [Date]).

Pete

[MatCienk]

I did exactly what You said. After adjusting the code to:

_overlapCount =

VAR __cDate = MAX(Kalendarz[Date])

RETURN

CALCULATE(

    COUNT(Zadania[msdyn_resourceassignment.msdyn_bookableresourceidname]),

    KEEPFILTERS(__cDate >= Zadania[Start]),

    KEEPFILTERS(__cDate <= Zadania[Finish])

)

Table just stop showing anything.

[BA_Pete]

Is the calendar disconnected in your model view, or has Power BI automatically created a relationship to your fact table without telling you?

I've attached a working PBIX file below so you can see how I set it up.

Pete

[MatCienk]

File really helped .

So what I was doing wrong is not connecting the calendar [date] to the table and that is why it didn't show me any data.

Now it works almost perfectly, here are the bugs remaining:

- It didn't catch the colission on date 30.05:

- It detected collision that do not exist ("day 6" - 2023-04-06):

I owe You for solving this case 🙂

[BA_Pete]

Are you able to share a more complete example dataset for April and May 2023 please?

These missed/added entries shouldn't be happening so I want to be able to understand what the issue is.

If the source data is very large, then follow these steps to make sharing here easy:

Filter your source table to just these months and this employee then remove all columns except [resource], [startDate], and [endDate], then copy the whole table in Power Query:

Then go to the Home tab > Enter Data and paste the table into there.

Lastly, open Advanced Editor for your new 'Enter Data' table, copy all of the code in there and paste it into a code window ( </> button ) here.

Thanks,

Pete

[MatCienk]

let
    Źródło = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZS/CoMwEMZfRZwNXC7+nTt36SoODg4hoAUrhc59tb5XJcYYK02MUDp+8P0ul/suKcsQAZFASjALo7U417dm6B/BiTet6O694GEVzUBGINeAElagIJBoQInvAJMeOgOUEpQndC0XdXAZrvz17EV90D46kCDTdiWs7VA9oUVYAUYY1YASViAmuADxyNgv7G0HtrHb24HUBJyBfQKpG6CZAVDnSMcrgwnYl247I/TNYAJ2Z+Cun5hLkUwr6wDmZ7OI/wO5CThTOAAwMAAK9hR+bcfCsEvh070U/sDu/t318+k3XIlt/eoN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Finish = _t, Start = _t, Resource = _t]),
    #"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"Finish", type date}, {"Start", type date}, {"Resource", type text}})
in
    #"Zmieniono typ"

I also tried to import this date to Your example file, but I failed, don't know what I am doing wrong (I have replaced the colums in the code properly). 

[BA_Pete]

I'm not sure I see the issue here.

This is 06/04/2023 - 2 x Mateusz collisions and 4 x Monika collisions:

This is 30/05/2023 - 3 x Mateusz, 6 x Monika:

Have I misunderstood the requirements here?

I've attached the updated PBIX below. Updated data on the 'New' tab.

Pete

[MatCienk]

I think You are 100% correct.

This might be the visualisation issue then.

I have found such note on the calendar I am using:

"f an event lasts for more than a day and the start and end times are before 9 AM, the end date of the event in the visual will be a day before the end date given in the data"

Maybe this is the casue.

Case closed. Big thanks to You!

[BA_Pete]

No problem, glad we got it sorted 🙂

Pete

[BA_Pete]

Example data M code for future:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkotzi8tSk5VcFTSUTLUNdQ1MjA0ADKNDWDsWB0kVU5EqXIGqzKCqTIygLFRVIFtNEAYZmiKx0qiDMOqKhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [resource = _t, startDate = _t, finishDate = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"resource", type text}, {"startDate", type date}, {"finishDate", type date}})
in
    chgTypes