Forum Discussion

MatCienk's avatar
MatCienk
Regular Visitor
3 years ago
Solved

Finding common part of Dates dataset

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 some...
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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

BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
3 years ago

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's avatar
MatCienk
Regular Visitor
3 years ago

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's avatar
    BA_Pete
    Icon for Super User rankSuper User
    3 years ago

     

    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's avatar
      MatCienk
      Regular Visitor
      3 years ago

      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's avatar
        BA_Pete
        Icon for Super User rankSuper User
        3 years ago

         

        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