Spreading amounts between dates and then summing each date

[Walt1010]

I have a fact table with employee, sickness start date, sickness end date and sickness hours.

MASTER TABLE

employee sickness start date sickness end date sickness hours Sickness hours/day

A Jones     20/1/2024                23/1/2024            15                      15/4

B Smith      15/5/2024               15/5/2024             7.5                     7.5/1

I wish to spread the sickness hours linearly between the 2 dates, but only including working days. I have created a date table and filtered out all but the working days, and I've added a column to the fact table where I've calculated the number of sickness hours per working day. As a next step I thought I would, for each row of the fact table, filter the date table by date and sum all the sickness hours that are eligible, for each employee. I tried doing this using a measure with FILTER and SUMX, but I got a scalar error on the filter. Can anyone help?

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

It is for creating a new table.

expected result table = 
	VAR _calendar = ADDCOLUMNS(
		CALENDAR(
			DATE(YEAR(MIN(data[sickness_start_date])), 1, 1),
			DATE(YEAR(MAX(data[sickness_end_date])), 12, 31)
		),
		"@weekday", FORMAT(
			[Date],
			"ddd"
		)
	)
	VAR _excludeweekend = FILTER(
		_calendar,
		NOT [@weekday] IN {
			"Sat",
			"Sun"
		}
	)
	VAR _datatable = GENERATE(
		data,
		FILTER(
			_excludeweekend,
			[Date] >= data[sickness_start_date] && [Date] <= data[sickness_end_date]
		)
	)
	VAR _dayscount = ADDCOLUMNS(
		_datatable,
		"@dayscount", COUNTROWS(FILTER(
			_datatable,
			data[employee] = EARLIER(data[employee])
		))
	)
	VAR _result = ADDCOLUMNS(
		_dayscount,
		"@sickness_hours_per_day", DIVIDE(
			data[sickness_hours],
			[@dayscount]
		)
	)

	RETURN
		SUMMARIZE(
			_result,
			data[employee],
			data[sickness_start_date],
			data[sickness_end_date],
			[Date],
			[@weekday],
			[@sickness_hours_per_day]
		)