topic Re: calculate hours within selected daterange in DAX Commands and Tips

calculate hours within selected daterange

StefanH74 — Mon, 05 Aug 2024 08:52:51 GMT

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

Re: calculate hours within selected daterange

SachinNandanwar — Mon, 05 Aug 2024 09:04:00 GMT

Please post some sample data to help you better.

Re: calculate hours within selected daterange

Anonymous — Mon, 05 Aug 2024 10:37:26 GMT

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.

Re: calculate hours within selected daterange

StefanH74 — Mon, 05 Aug 2024 10:56:34 GMT

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)

Re: calculate hours within selected daterange

Anonymous — Mon, 05 Aug 2024 11:12:43 GMT

Share the sample data