DAX Help: Measure of Working Minutes between two times

eaglesilo — Thu, 07 Mar 2024 21:48:34 GMT

Howdy there. I'm stuck.

I'm trying to build a DAX measure that will calculate the working minutes between two datetime objects but just can't figure it out. Has anyone solved this before? I feel like it should be easy, I just can't wrap my head around the solution.

Below is my attempt, but it's returning working minutes for the entire day, not just between the start and end time for each day:

For the above records, here's what I expect the value to show:

Id	CreatedTime	postedTime	workingMinutes 0700-1630
113670	2024-02-27 11:55:00.000 -06:00	2024-02-28 10:06:36.000 -06:00	461
113720	2024-02-28 06:59:00.000 -06:00	2024-02-28 09:09:32.000 -06:00	129
113672	2024-02-27 11:56:00.000 -06:00	2024-02-27 12:08:16.000 -06:00	12
113662	2024-02-27 10:57:00.000 -06:00	2024-02-27 11:14:24.000 -06:00	17
113601	2024-02-26 15:31:00.000 -06:00	2024-02-27 07:49:56.000 -06:00	108
113361	2024-02-22 11:53:00.000 -06:00	2024-02-26 07:31:38.000 -06:00	878
113359	2024-02-22 11:44:00.000 -06:00	2024-02-22 14:53:08.000 -06:00	189
113340	2024-02-22 09:58:00.000 -06:00	2024-02-22 14:33:42.000 -06:00	275
113392	2024-02-22 13:19:00.000 -06:00	2024-02-22 13:21:34.000 -06:00	2
113195	2024-02-20 13:02:00.000 -06:00	2024-02-20 13:14:18.000 -06:00	12
113142	2024-02-20 09:02:00.000 -06:00	2024-02-20 10:12:01.000 -06:00	70

Working Hours are typically defined as 0700-1630 CST.

Here's the DAX I was trying so far, but I know it's, to an extent, not even directionally accurate for how I'm supposed to do it. (In the above screenshot, I expect the Total line to be the SumTotal of each individual Load Id value.)

_m_CreatedPosted_Time_cgpt = 
var createdTime = min(####Data[createdTime]) 
var PostedTime = min(###LoadPostingID[PostedTime])
RETURN
-- Calculate the total working minutes between StartDateTime and EndDateTime
SUMX (
    -- Generate a row for each date between StartDateTime and EndDateTime
        CALCULATETABLE (
        Calendar_DateRangePicker,
        DATESBETWEEN (
            Calendar_DateRangePicker[DateId],
            createdTime,
            PostedTime
        ),
        Calendar_DateRangePicker[IsBusinessDay####] = TRUE ()
    ),
    -- For each date, calculate the working minutes based on business hours
    IF (
        Calendar_DateRangePicker[DateId] = createdTime && HOUR(createdTime) < hour(Calendar_DateRangePicker[StartTime]), // If createdTime is before 07:00
        IF (
            HOUR(postedTime) >= hour(Calendar_DateRangePicker[EndTime]), // If postedTime is after or at 16:30
            DATEDIFF(Calendar_DateRangePicker[StartTime], Calendar_DateRangePicker[EndTime], MINUTE),
            DATEDIFF(Calendar_DateRangePicker[StartTime], postedTime, MINUTE)
        ),
        IF (
            Calendar_DateRangePicker[DateId] = postedTime && HOUR(postedTime) >= hour(Calendar_DateRangePicker[EndTime]), // If postedTime is after or at 16:30
            DATEDIFF(Calendar_DateRangePicker[StartTime], Calendar_DateRangePicker[EndTime], MINUTE),
            DATEDIFF(Calendar_DateRangePicker[StartTime], Calendar_DateRangePicker[EndTime], MINUTE)
        )
    )
)

I do have a Calendar table which includes the Start and End working time for each day, and indicates if a day is a Business Day. (The business hours are generally 0700-1630.)

Here's the relevant tables from the data model:

I feel the answer in here is something with a Calculated Table, but I can't figure out how that is supposed to work.

*After I get the answer I want here, I'll then get the Count of values so I can run the Average. I do want to have a measure that's just Totals, though, so I could use that for other things if needed, for why I'm not trying to get the Average directly in this DAX.

Appreciate any help you can provide!

*This is a crosspost to the below Reddit Post: https://www.reddit.com/r/PowerBI/comments/1b9608h/dax_help_measure_of_working_minutes_between_two/

Re: DAX Help: Measure of Working Minutes between two times

lbendlin — Sat, 09 Mar 2024 01:40:16 GMT

You say working minutes but your second timestamp is on seconds granularity. Please clarify. As you can see we have some slight disagreements on the duration.

You need to decide if you want to include or exclude the minute of the postedTime timestamp.

What should happen when the time interval spreads over weekends and holidays? For now I assume you observe weekends on saturday and sunday, and have no holidays.

wm = VAR a = GENERATESERIES ( ROUNDDOWN ( [CreatedTime] * 1440, 0 ), ROUNDDOWN ( [postedTime] * 1440, 0 ) ) VAR b = ADDCOLUMNS ( FILTER ( CALENDAR ( [CreatedTime], [postedTime] ), WEEKDAY ( [Date], 2 ) < 6 ), "v", ROUNDDOWN ( [Date], 0 ) * 1440 ) VAR c = GENERATE ( b, GENERATESERIES ( [v] + 420, [v] + 990 ) ) RETURN COUNTROWS ( INTERSECT ( a, SELECTCOLUMNS ( c, "Value", [Value] ) ) )

Re: DAX Help: Measure of Working Minutes between two times

eaglesilo — Wed, 13 Mar 2024 11:54:54 GMT

Thank you for the reply. As you can see in my table, I have a Calendar_DateRangePicker[IsBusinessDay] field which takes care of Working vs Non Working days.

In the various screenshots, I did not truncate the times to the nearest minute, but if we need to do that before we can proceed with getting to working minutes, we can. You can choose to round in whichever direciton you wish for now.

Re: DAX Help: Measure of Working Minutes between two times

lbendlin — Wed, 13 Mar 2024 14:51:41 GMT

As you can see in my table, I have a Calendar_DateRangePicker[IsBusinessDay] field which takes care of Working vs Non Working days.

You can modify my code accordingly to replace the WEEKDAY filter with your IsBusinessDay filter.

topic Re: DAX Help: Measure of Working Minutes between two times in DAX Commands and Tips

DAX Help: Measure of Working Minutes between two times

Re: DAX Help: Measure of Working Minutes between two times

Re: DAX Help: Measure of Working Minutes between two times

Re: DAX Help: Measure of Working Minutes between two times