Over flow of hours to next month

[Anonymous]

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.

[Anonymous · Accepted solution]

@Anonymous

This is a recursive calculation and can't be expressed simply as a sum/product of some terms since it entails the MAX function. You can't do it with DAX alone. You have to perform this calculation in Power Query. So, for each staff member and each month you have to calculate the hours. Such a table with 3 columns (StaffID, MonthID, Hours) will then be imported into PowerBI and joined to 2 dimensions (Staff, Dates). Then you'll be able to calculate what you need via a measure. Bear in mind that you'll have to deal with granularity issues properly since the table you'll create will have the date granularity of month, whereas your Dates table will be on the day granularity.

[amitchandak]

@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

https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/599712

[Anonymous]

@amitchandakthanks but I require the additional hours to be overflowed to the next month?

[amitchandak]

@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))

[Anonymous]

Just to point out that the date is in dd/MM/yyyy format. Staff Members are A, B, C and D.