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!
I'm interested in showing in a table visualization the amount of workers working each hour of the day. I succesfully managed to map the shifts that happen within the same day with hours as rows and days as columns. I ran into into problems when trying to show shifts that spread over two or more days.
The way I'm visualizing is as follows
Monday | Tuesday | Wednesday | |
06-07 | 2 | ||
07-08 | 1 | 3 | 1 |
08-09 | 2 | ||
09-10 | 1 | 1 |
and the sample data whch I haven't managed to visualized could be as follows
Startdate | Starttime | Enddate | Endtime |
3.7.2017 | 22:00 | 6.7.2017 | 6:00 |
30.6.2017 | 8:00 | 30.6.2017 | 14:00 |
30.6.2017 | 18:00 | 2.7.2017 | 2:00 |
29.6.2017 | 10:00 | 30.6.2017 | 12:00 |
29.6.2017 | 20:00 | 3.7.2017 | 15:00 |
Any help would be greatly appreciated.
Solved! Go to Solution.
Hi @Kerma,
You can refer to below sample to get hour - weekday analysis visual:
Sample:
1. Add custom columns to convert columns to datetime.
2. Add custom column to get datetime records between 'start datetime' and 'end datetime'.
#"Added Custom1"= Table.AddColumn(#"Changed Type1", "Datetime List", each List.DateTimes([Start Datetime],Duration.TotalHours(Duration.From([End Datetime]-[Start Datetime])), #duration(0, 1, 0, 0)))
3. Keep related columns and expand these records.
4. Save and return to report view.
5. Add calculated columns to show weekday and hour.
HOUR = HOUR([Datetime List]) WeekDay = FORMAT([Datetime List],"dddd")
5. Create matrix visual with above calculate columns.
6. Add a table preview visual as the slicer.
After above steps, you can use table preview visual to choose the analysed records.
Notice: I also upload this sample file as the attachments.
Regards,
XXiaoxinSheng
@Kermayou could merge the date and hour column to obtain one type of start date and end date. After that, you can use:
DATEDIFF(<start_date>, <end_date>, <interval>)
I need to map the results in a day-hour visualization shown above. For example a shift from 3.7.2017 20:00 to 5.7.2017 08:00 would show 1 for each hour on 3.7.2017 after 20:00, 1 for each hour of 4.7.2017 and 1 for hours 00-08 on 5.7.2017.
Hi @Kerma,
You can refer to below sample to get hour - weekday analysis visual:
Sample:
1. Add custom columns to convert columns to datetime.
2. Add custom column to get datetime records between 'start datetime' and 'end datetime'.
#"Added Custom1"= Table.AddColumn(#"Changed Type1", "Datetime List", each List.DateTimes([Start Datetime],Duration.TotalHours(Duration.From([End Datetime]-[Start Datetime])), #duration(0, 1, 0, 0)))
3. Keep related columns and expand these records.
4. Save and return to report view.
5. Add calculated columns to show weekday and hour.
HOUR = HOUR([Datetime List]) WeekDay = FORMAT([Datetime List],"dddd")
5. Create matrix visual with above calculate columns.
6. Add a table preview visual as the slicer.
After above steps, you can use table preview visual to choose the analysed records.
Notice: I also upload this sample file as the attachments.
Regards,
XXiaoxinSheng
This looks very good, I will try this out! Thanks a lot 🙂
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |