Forum Discussion

Florie's avatar
Florie
Helper I
1 year ago
Solved

Created a calculated column using 2 date/time fields

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

Need Help
  • burakkaragoz's avatar
    burakkaragoz
    1 year ago

    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

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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)

  • burakkaragoz's avatar
    burakkaragoz
    Super User
    1 year ago

    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