Visualize Actual and Available Daily Working Hours of a Team with Empty Days

[Edgard]

Hello Community,

I have a dataset of Workers activities in the following format:

Activity ID	DateTime Start	DateTime End	User
1	10/09/2019 12:51:06	12/09/2019 15:51:25	USER1
2	11/09/2019 10:21:59	13/09/2019 17:01:12	USER2

With a script I have made a new table wich counts the daily working hours for each date and user (for example, from 7am to 6pm), wich would end like this for the previous example:

Date	Activity ID	User	Hours
10/09/2019	1	USER1	5
11/09/2019	1	USER1	10
11/09/2019	2	USER2	7.5
12/09/2019	1	USER1	2
12/09/2019	2	USER2	10
13/09/2019	2	USER2	1

Now, here are the problems I'm facing:

1. I need to display Total Working Hours vs Date, but I need to also show the days when no activity was registered. I partially managed to do this by creating a calendar, but when I use a slicer to filter the dates from (for example) day 1 to 5, if day 1 or day 5 doesn't have registered activities, they don't show. Any other day in between is shown in the Bar Graph even with no activity registered.

2. I have a meter with Actual Working Hours (SUM of worked hours in the days) and Available Working Hours (# of working days * # of users * 10), but then again, it's not calculating the days with no activities for the Available Working Hours. I'm calculating this with a measure 

Available Hours = CALCULATE(DISTINCTCOUNT('Users'[User Alias]))*10*CALCULATE(DISTINCTCOUNT(Calendar[Date]),Calendar[Day]<6)

I hope you can help me, please tell me if I need to clarify anything in my question.

Thanks.

[amitchandak]

As the calendar is joined with your table having working day data, it might not be giving all day.

Create on more calendar tables and join it with the calendar table. Here you can also add working hours.

You can try something like this to get active users in this new table. But this work with the joined the tables.  To populate back the active users on that day.

Example code. to be created as a column

Max Sales order id = Maxx(filter(sales,Sales[item_id]='Item'[Item ID]),Sales[Order Id]) 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Edgard · Accepted solution]

Thanks for the suggestion of using another calendar. It help me to notice that the problem was the bilateral correlation. So the solution was actualy to change the Relationship from Both to Single.