Created a calculated column using 2 date/time fields

[Florie]

Hi.

I'm trying to create a calculated column in a table to give a yes/no (or 1/0) to show if the difference between 2 date/time fields is <= 24 hours.

I need to exclude weekends and bank holidays - I have a dimdate table with weekends and bank holidays flagged.
A working day is 24 hours.
I'm having problems writing the dax to do this due to the time element.
This is an example of fields and output:

Any ideas on how to calcuate the 'In timescale' column? Many thanks

[Florie]

Thank you!

[burakkaragoz · Accepted solution]

Hi @Florie 

Great question! Calculating a “within timescale” column that excludes weekends and bank holidays (using a dimdate table) is a classic DAX challenge. Here’s a step-by-step method:

---

Step 1: Prepare Your DimDate Table

• Ensure your DimDate table covers all possible dates and includes:
  • [IsWeekend] column (TRUE/FALSE)
  • [IsBankHoliday] column (TRUE/FALSE)

---

Step 2: Calculated Column for Working Hours Difference

You’ll need to sum hours on valid working days between [Start] and [Completion], ignoring weekends and bank holidays.

Here’s a sample DAX calculated column for your table (let’s call it [In Timescale]):

dax

In Timescale =
VAR StartDateTime = [Start]
VAR EndDateTime = [Completion]
VAR DatesBetween =
    FILTER (
        'DimDate',
        'DimDate'[Date] >= DATEVALUE(StartDateTime)
        && 'DimDate'[Date] <= DATEVALUE(EndDateTime)
        && 'DimDate'[IsWeekend] = FALSE()
        && 'DimDate'[IsBankHoliday] = FALSE()
    )
VAR WorkingHours =
    SUMX (
        DatesBetween,
        VAR ThisDate =
            'DimDate'[Date]
        VAR StartHour =
            IF (
                ThisDate = DATEVALUE(StartDateTime),
                HOUR(StartDateTime) + MINUTE(StartDateTime)/60,
                0
            )
        VAR EndHour =
            IF (
                ThisDate = DATEVALUE(EndDateTime),
                HOUR(EndDateTime) + MINUTE(EndDateTime)/60,
                24
            )
        RETURN
            EndHour - StartHour
    )
RETURN IF(WorkingHours <= 24, "Yes (or 1)", "No (or 0)")

---

Step 3: Handling Edge Cases

• If [Start] and [Completion] are on the same day, just subtract the times.
• If they span multiple days, the first and last day use partial hours; full days in between count as 24 hours each.

---

Notes

• Adjust the formula if your working day isn’t 24h (e.g., 8h shifts).
• For “weekend” output, you can add a check:
  If all days between start and completion are flagged as weekends in DimDate, output “weekend”.

---

Let me know if you need the DAX for a specific edge case or have a different working hours definition!
translation and formatting supported by AI

[bhanu_gautam]

@Florie Create a calculated column to determine if the difference between Start and Completion is within 24 hours, excluding weekends and bank holidays.

dax
InTimescale =
VAR StartDateTime = 'YourTable'[Start]
VAR CompletionDateTime = 'YourTable'[Completion]
VAR StartDate = DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime))
VAR CompletionDate = DATE(YEAR(CompletionDateTime), MONTH(CompletionDateTime), DAY(CompletionDateTime))
VAR WorkingDays =
CALCULATE(
COUNTROWS('DimDate'),
'DimDate'[Date] >= StartDate,
'DimDate'[Date] <= CompletionDate,
'DimDate'[IsWorkingDay] = TRUE()
)
VAR TotalHours =
IF(
WorkingDays = 1,
DATEDIFF(StartDateTime, CompletionDateTime, HOUR),
(WorkingDays - 2) * 24 +
(24 - HOUR(StartDateTime) - MINUTE(StartDateTime) / 60) +
HOUR(CompletionDateTime) + MINUTE(CompletionDateTime) / 60
)
RETURN
IF(TotalHours <= 24, 1, 0)