calculate hours within selected daterange

[StefanH74]

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

[Rakesh1705]

Share the sample data

[Rakesh1705 · Accepted solution]

Since I have not any available data, I have created one data set to demonstrate the solution
Source data

Creating a Duplicate of the same and rename it as "Start End". Removing start time and end time

Added a custom column to create a calendar with the in between dates of start and end dates

Expanded table got 103 rows whereas original table has 18 rows

Overall hours is calculated in the main table

Average hours is calculated

With the help of lookupvalue formula hours per day against each work in loaded in "Start End" Table

Final Visualization

If this is ok with you please accept the same as your solution.

[StefanH74]

Hello Rakesh,

thanx for your help so far. It is certainly helpful.

and even though it is close, it is not exactly right.

for example, if I would have a run from Jan 1, 0:00 until Jan 2 8:00, and I set my slicer to start on Jan 2 then your solution give me 16 hours (the average of 1 day) where I would need to see 8 hours (Jan 2, from 0:00 until 8:00)

[SachinNandanwar]

Please post some sample data to help you better.