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.
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.
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 |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |