Need help writing a DAX command which SUMs a value IFF it is between the start and end times.

[iKitKat]

Hello all,

I have been given an assigned where I need to create a report tracking room utilisation. One of the charts need to show the total booked hours ( SUM(Duration) ) at any given time, day, week and year. I (think I have) created the necessary custom date and time tables for hierachical drill through but I am unable to figure out how to satisfy the main requirement of my assignment: how to show the total booked hours at any given time slice.

Here is a simplified example of two booking records for a room.

I am free to use any suitable chart but it must show the total booked hours like this:

The psuedo logic is sum the duration if and only if it is equal to the starting time or greater and if it is less than the ending time.

Start Time <= All valid time slices in between < End Time.

Thank you for your time.

[iKitKat]

@123abc Hello, thank you for the reply - the logic of the code is solid and makes sense. However, is there an alternative other than creating a combined date and time table to use this DAX?

[123abc]

If you want to avoid creating a combined date and time table, you can still achieve the same result by using a single date table and two separate columns, one for the start time and one for the end time. This approach assumes that you are working with a single date column and separate time columns. Here's a modified DAX measure for this scenario:

Assuming you have a table called Bookings with columns Booking Date, Start Time, End Time, and Duration, you can create a DAX measure like this:

Total Booked Hours =
VAR SelectedStartTime = SELECTEDVALUE('Date Table'[Selected Start Time])
VAR SelectedEndTime = SELECTEDVALUE('Date Table'[Selected End Time])

RETURN
SUMX(
FILTER(
Bookings,
Bookings[Booking Date] + Bookings[Start Time] <= SelectedEndTime &&
Bookings[Booking Date] + Bookings[End Time] >= SelectedStartTime
),
Bookings[Duration]
)

In this modified measure, we combine the Booking Date and Start Time columns and Booking Date and End Time columns to create datetime values for comparison. This allows you to filter the bookings based on the selected start and end times without needing a separate combined date and time table.

You can use this modified measure with a single date table and your existing time columns to achieve the desired result without creating a combined date and time table.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[123abc · Accepted solution]

To achieve your goal in Power BI using DAX, you can create a measure that sums the Duration only if it falls within the specified time slice. You can use the FILTER function to filter the data based on your condition. Here's a DAX formula to create the measure:

Total Booked Hours =
SUMX(
FILTER(
'YourTable',
'YourTable'[Start Time] <= MAX('Calendar'[Time]) && 'YourTable'[End Time] >= MIN('Calendar'[Time])
),
'YourTable'[Duration]
)

In this formula:

• 'YourTable' is the name of the table where your booking records are stored.
• 'Calendar' is the name of your date and time table. Replace it with the name of your custom date and time table.

This measure sums the Duration for all rows in the 'YourTable' where the Start Time is less than or equal to the maximum time in your date and time table (MAX('Calendar'[Time]) and the End Time is greater than or equal to the minimum time in your date and time table (MIN('Calendar'[Time]).

You can use this measure in your visualizations to display the total booked hours for the specified time slices (e.g., by day, week, or year) based on your custom date and time table.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.