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 a table of H&S incidents with lost time hours due to these incidents:
Incident# | Date | Staff | Lost Time Hours |
1 | 10/03/2020 | A | 2 |
2 | 12/06/2020 | B | 240 |
3 | 1/07/2020 | C | 3 |
4 | 24/08/2020 | D | 15 |
I would like to summarize/visualize Lost Time Hours by Month. The challenge I am having is an incident may have more Lost Time Hours than the number of working hours (assume 160 hours) per staff, per month. I would like the exceeding hours to be overflowed to the next month (and the next etc.)
Is there any way to do this in DAX or in Power BI in general? The result I am expecting is similar to below:
Year | Month | Lost Time Hours | Comment |
2020 | 03 | 2 | |
2020 | 04 | 0 | |
2020 | 05 | 0 | |
2020 | 06 | 160 | Maximum of 160 working hours in a month |
2020 | 07 | 83 | 3hrs + 80 hrs from previous month |
2020 | 08 | 15 |
The limit of 160 hours is per staff member, so if there are 2 incidents involving 2 different staff members in that month the limit should be based on each of the staff member.
Solved! Go to Solution.
@Anonymous , Join with date table and you can have month year here and try a formula like this
sumx(summarize(Table, Date[month year], table[Staff], "_1",sum(Table[Lost Time Hours])),if([_1] >160,160,[_1]))
For date format refer
@amitchandakthanks but I require the additional hours to be overflowed to the next month?
@Anonymous , Try like
sumx(summarize(Table, Date[month year], table[Staff], "_1",sum(Table[Lost Time Hours]), "_2",calculate(sum(Table[Lost Time Hours]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))),if([_1] >160,160,[_1])+ if([_2] >160,[_2]-160,0))
Just to point out that the date is in dd/MM/yyyy format. Staff Members are A, B, C and D.
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |