calculate hours within selected daterange

Hello all,

I have a fact-table with productionruns which contains the start- and end-date & time.

now to calculate the production-duration would be as simple as making a caclulated column

PrunTotalTime = vProductionRun[end]-vProductionRun[start]

and then SUM that column

however the problem lies within the fact that these runs can be quite long, in fact longer than the period I want to do calculations on.

I have a Date-table, and a slicer to select the daterange I want to measure.

so my calculation should be something like:

PrunTotalTime = MIN(vProductionRun[end],MAX(dimDate,[Date]))-MAX(vProductionRun[start],MIN(dimDate,[Date]))

but it needs to do that for all lines seperately, and I cant get that to work.

as a measure it wont work because it doesnt look at all lines seperately.

as a calculated column it doesnt work because it ignores the setting of the date-slicer and just takes the min- and max-date of the entire date-table

any ideas on how to do this would be greatly appreciated!

Thanx in advance for your contribution,

Stefan