Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have an interesting oen that I don't know how to solve.
The data looks something like this:
ID Revision Start Date & Time End Date & Time
1 1 2016/02/03 14:26 2016/02/06 13:58
1 2 2016/02/06 13:58 2016/08/03 9:06
2 1 2016/02/11 15:21 2016/02/11 16:55
2 2 2016/02/11 16:55 2016/02/12 8:55
2 3 2016/02/12 8:55 2016/06/29 15:10
2 4 2016/06/29 15:10 2016/07/05 9:30
2 5 2016/07/05 9:30 2017/01/03 15:00
The table contains around 150k lines.
The request is :
So initial output I would require to get this working is just Month/Year - total hours. From there I should break it down into per ID etc.
I'm not sure how to implement the monthly calculations? Can anybody help?
Solved! Go to Solution.
@qball wrote:
I have an interesting oen that I don't know how to solve.
The data looks something like this:
ID Revision Start Date & Time End Date & Time
1 1 2016/02/03 14:26 2016/02/06 13:58
1 2 2016/02/06 13:58 2016/08/03 9:06
2 1 2016/02/11 15:21 2016/02/11 16:55
2 2 2016/02/11 16:55 2016/02/12 8:55
2 3 2016/02/12 8:55 2016/06/29 15:10
2 4 2016/06/29 15:10 2016/07/05 9:30
2 5 2016/07/05 9:30 2017/01/03 15:00
The table contains around 150k lines.
The request is :
- Report total hours that elapsed per ID or ID and Revision in hours (actually working hours but that is not the problem right now)
- Break down those hours per month, even if the start and end date crossed many months, it needs to report the maximum for those months.
So initial output I would require to get this working is just Month/Year - total hours. From there I should break it down into per ID etc.
I'm not sure how to implement the monthly calculations? Can anybody help?
To achieve the goal, I'll create a calendar table as below
calendar = ADDCOLUMNS ( FILTER ( CALENDAR ( "2016-01-01", "2017-12-31" ), DAY ( [Date] ) = 1 ), "MonthEnd", EOMONTH ( [Date], 0 ) )
Then create a calculated table as below, to expand the dates window to multiple rows for each month.
calculated Table = FILTER ( CROSSJOIN ( yourTable, 'calendar' ), OR ( OR ( yourTable[Start Date & Time] >= 'calendar'[Date] && yourTable[Start Date & Time] <= 'calendar'[MonthEnd], 'calendar'[MonthEnd] >= yourTable[Start Date & Time] && 'calendar'[MonthEnd] <= yourTable[End Date & Time] ), ( yourTable[End Date & Time] ) >= 'calendar'[Date] && yourTable[End Date & Time] <= 'calendar'[MonthEnd] ) )
Then create a calculated column as
elapsed hours = SWITCH ( TRUE (), 'calculated Table'[Start Date & Time] >= 'calculated Table'[Date] && 'calculated Table'[End Date & Time] <= 'calculated Table'[MonthEnd], DATEDIFF ( 'calculated Table'[Start Date & Time], 'calculated Table'[End Date & Time], HOUR ), 'calculated Table'[Start Date & Time] >= 'calculated Table'[Date] && 'calculated Table'[End Date & Time] >= 'calculated Table'[MonthEnd], DATEDIFF ( 'calculated Table'[Start Date & Time], 'calculated Table'[MonthEnd], HOUR ), 'calculated Table'[Date] > 'calculated Table'[Start Date & Time] && 'calculated Table'[MonthEnd] < 'calculated Table'[End Date & Time], DATEDIFF ( 'calculated Table'[Date], 'calculated Table'[MonthEnd], HOUR ), DATEDIFF ( 'calculated Table'[Date], 'calculated Table'[End Date & Time], HOUR ) )
So finally
See more details in the attached pbix file.
Eric - you are a legend!
Thank you so much - all working now. Also got it to work by calculating the working hours (minus weekends and holidays).
Glad to help. 🙂
@qball wrote:
I have an interesting oen that I don't know how to solve.
The data looks something like this:
ID Revision Start Date & Time End Date & Time
1 1 2016/02/03 14:26 2016/02/06 13:58
1 2 2016/02/06 13:58 2016/08/03 9:06
2 1 2016/02/11 15:21 2016/02/11 16:55
2 2 2016/02/11 16:55 2016/02/12 8:55
2 3 2016/02/12 8:55 2016/06/29 15:10
2 4 2016/06/29 15:10 2016/07/05 9:30
2 5 2016/07/05 9:30 2017/01/03 15:00
The table contains around 150k lines.
The request is :
- Report total hours that elapsed per ID or ID and Revision in hours (actually working hours but that is not the problem right now)
- Break down those hours per month, even if the start and end date crossed many months, it needs to report the maximum for those months.
So initial output I would require to get this working is just Month/Year - total hours. From there I should break it down into per ID etc.
I'm not sure how to implement the monthly calculations? Can anybody help?
To achieve the goal, I'll create a calendar table as below
calendar = ADDCOLUMNS ( FILTER ( CALENDAR ( "2016-01-01", "2017-12-31" ), DAY ( [Date] ) = 1 ), "MonthEnd", EOMONTH ( [Date], 0 ) )
Then create a calculated table as below, to expand the dates window to multiple rows for each month.
calculated Table = FILTER ( CROSSJOIN ( yourTable, 'calendar' ), OR ( OR ( yourTable[Start Date & Time] >= 'calendar'[Date] && yourTable[Start Date & Time] <= 'calendar'[MonthEnd], 'calendar'[MonthEnd] >= yourTable[Start Date & Time] && 'calendar'[MonthEnd] <= yourTable[End Date & Time] ), ( yourTable[End Date & Time] ) >= 'calendar'[Date] && yourTable[End Date & Time] <= 'calendar'[MonthEnd] ) )
Then create a calculated column as
elapsed hours = SWITCH ( TRUE (), 'calculated Table'[Start Date & Time] >= 'calculated Table'[Date] && 'calculated Table'[End Date & Time] <= 'calculated Table'[MonthEnd], DATEDIFF ( 'calculated Table'[Start Date & Time], 'calculated Table'[End Date & Time], HOUR ), 'calculated Table'[Start Date & Time] >= 'calculated Table'[Date] && 'calculated Table'[End Date & Time] >= 'calculated Table'[MonthEnd], DATEDIFF ( 'calculated Table'[Start Date & Time], 'calculated Table'[MonthEnd], HOUR ), 'calculated Table'[Date] > 'calculated Table'[Start Date & Time] && 'calculated Table'[MonthEnd] < 'calculated Table'[End Date & Time], DATEDIFF ( 'calculated Table'[Date], 'calculated Table'[MonthEnd], HOUR ), DATEDIFF ( 'calculated Table'[Date], 'calculated Table'[End Date & Time], HOUR ) )
So finally
See more details in the attached pbix file.
Thank you Eric - will do it right now and let you know the outcome.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |