Help! List of Jobs Worked Between Two Dates

[StefanM]

Hi everyone! 

I am hitting a roadblock trying to create a second table inside PBI based on the workbook we're feeding into it. We've got a list of Start & End dates for work being done by people in unique roles. We want this second table to show every date duplicated for each unique role type, showing how many of that role type are included in the date range - to eventually be filtered by a date table slicer. I am just not sure how to do this inside PBI itself.  Can anyone help? 

Here are the examples I am working with. 

There are 9 unique roles (Top left table) in the sample data set (bottom left table). I want to create a table showing all dates with all roles next to a column counting how many times that unique role was mentioned on that date. Sometimes it could be zero mentions, other times it could be loads of them. New roles might be added to the system at some point too which makes it more complicated. 

Can anyone help with this? Im totally stuck right now! I've attached my PBI file too! https://easyupload.io/ugbh3w
Im not sure what the best platform to upload here is, so just LMK if a specific platform is preferred 🙂 

[StefanM]

Going to try bumping this one - still working on it, but cant find any good solutions to get the end result I need. 

Can anyone see a good solution, or let me know what I can provide to help make it easier to provide a solution?

[StefanM]

Hi @Anonymous ! 

Thanks for helping out. This gets me one step closer, but it only numbers 1 for a role when it should number as two. Mechanic USA Male should show up as 2 on the 25th & 26th, due to the first entries End Date lasting until the 26th.

Perhaps detailing the end result could help. Maybe I am going about it totally wrong. I want to use a date slicer to show filter a table that shows how many roles have been worked during that timeframe. 

If the Slicer was set to: 24th - 24th of May, in would output:

01 - Role	02 - Location	03 - Gender	04 - Count
Mechanic	USA	Male	1
Mechanic	Germany	Male	0
Mechanic	Germany	Female	0
Surfer	Italy	Unknown	0
Surfer	USA	Male	0
Surfer	Canada	Female	0
Builder	Canada	Female	0
Builder	Italy	Female	0
Builder	Ireland	Unknown	0

But if the slicer was set to 24th - 29th, it would show: 
 

01 - Role	02 - Location	03 - Gender	04 - Count
Mechanic	USA	Male	2
Mechanic	Germany	Male	1
Mechanic	Germany	Female	0
Surfer	Italy	Unknown	0
Surfer	USA	Male	0
Surfer	Canada	Female	0
Builder	Canada	Female	0
Builder	Italy	Female	0
Builder	Ireland	Unknown	0

Is this possible to do with Power BI? I can also see that you attached the PBIX file to your message, is that something any user can do on these forums?

Many thanks in advance! 

[Anonymous]

Hi @StefanM ,

Please refer to this blog to provide test files, please note the erasure of sensitive data.
How to provide sample data in the Power BI Forum

Best Regards,
Gao
Community Support Team

[Anonymous]

Hi @StefanM ,

Please new a table first:

Table3 = GENERATE(VALUES('Table1'[01 - First Date]),'Table2')

Then new a column:

Count = 
VAR _date = 'Table3'[01 - First Date]
VAR _role = 'Table3'[01 - Role]
VAR _location = 'Table3'[02 - Location]
VAR _gender = 'Table3'[03 - Gender]
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Table1' ),
        'Table1'[01 - First Date] = _date
            && 'Table1'[04 - Role] = _role
            && 'Table1'[05 - Location] = _location
            && 'Table1'[06 - Gender] = _gender
    ) + 0
RETURN
    _count

Result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data