Created a calculated column using 2 date/time fields

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]):

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