Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

DAX Workday Function

Hello!

 

I want to create the equivalent of the workday formula in DAX. I have created a table of standard holidays and weekends where I have a column called "ISWORKINGDAY" where non-weekends/holidays are a value of 1 (0 for weekends and holidays). I used it for one formula already when I needed to count the number of working days between two dates and that was easy enough. But now I need to calculate when an item's free storage time will end. So I have the starting date + the free time length (say 4 working days) and I need to calculate the last free day.

 

Any ideas? The reason I need this is the billing of the item is calendar after free time expires.

  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Hi Anonymous 

    I would suggest adding some Working Day Index columns to your Date table.

    Below is an example using DAX.

    • Working Day Index = an index that increases by one on each Working Day, and is blank on non-Working Days.
    • Last Working Day Index = Working Day Index, except blanks are converted to the most recent Working Day Index (so that weekends/holidays have the same index as the most recent Working Day).

    You can then find the nth Working day after a given date by adding n to Last Working Day Index, and applying this as a filter on Working Day Index.

     

     

    Date = 
VAR StartDate =
    dt"2023-01-01"
VAR EndDate =
    dt"2023-12-31"
-- Sample Holidays
VAR Holidays =  
    {
        dt"2023-01-01",
        dt"2023-01-02",
        dt"2023-01-16",
        dt"2023-02-20",
        dt"2023-05-29",
        dt"2023-06-19",
        dt"2023-07-04",
        dt"2023-09-04",
        dt"2023-10-09",
        dt"2023-11-10",
        dt"2023-11-11",
        dt"2023-11-23",
        dt"2023-12-25"
    }

VAR DateBase = CALENDAR ( StartDate, EndDate )
VAR DateFinal =
    GENERATE (
        DateBase,
        VAR d = [Date]
        VAR IsWorkingDay = NETWORKDAYS ( d, d, 1, Holidays )
        VAR LastWorkingDayIndex =
            NETWORKDAYS ( StartDate, d, 1, Holidays )
        VAR WorkingDayIndex =
            IF (
                IsWorkingDay,
                LastWorkingDayIndex
            )
        RETURN
            ROW (
                "Is Working Day", IsWorkingDay,
                "Working Day Index", WorkingDayIndex,
                "Last Working Day Index", LastWorkingDayIndex
            )
    )
RETURN
    DateFinal

     

     

     

    Would something like that work for you?

    Regards

     

1 Reply

  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    Hi Anonymous 

    I would suggest adding some Working Day Index columns to your Date table.

    Below is an example using DAX.

    • Working Day Index = an index that increases by one on each Working Day, and is blank on non-Working Days.
    • Last Working Day Index = Working Day Index, except blanks are converted to the most recent Working Day Index (so that weekends/holidays have the same index as the most recent Working Day).

    You can then find the nth Working day after a given date by adding n to Last Working Day Index, and applying this as a filter on Working Day Index.

     

     

    Date = 
VAR StartDate =
    dt"2023-01-01"
VAR EndDate =
    dt"2023-12-31"
-- Sample Holidays
VAR Holidays =  
    {
        dt"2023-01-01",
        dt"2023-01-02",
        dt"2023-01-16",
        dt"2023-02-20",
        dt"2023-05-29",
        dt"2023-06-19",
        dt"2023-07-04",
        dt"2023-09-04",
        dt"2023-10-09",
        dt"2023-11-10",
        dt"2023-11-11",
        dt"2023-11-23",
        dt"2023-12-25"
    }

VAR DateBase = CALENDAR ( StartDate, EndDate )
VAR DateFinal =
    GENERATE (
        DateBase,
        VAR d = [Date]
        VAR IsWorkingDay = NETWORKDAYS ( d, d, 1, Holidays )
        VAR LastWorkingDayIndex =
            NETWORKDAYS ( StartDate, d, 1, Holidays )
        VAR WorkingDayIndex =
            IF (
                IsWorkingDay,
                LastWorkingDayIndex
            )
        RETURN
            ROW (
                "Is Working Day", IsWorkingDay,
                "Working Day Index", WorkingDayIndex,
                "Last Working Day Index", LastWorkingDayIndex
            )
    )
RETURN
    DateFinal

     

     

     

    Would something like that work for you?

    Regards