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.
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.
Solved! Go to 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.
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
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.
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |