Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating Days between dates

Hello All, 

 

I have 2 data sets - first is employees data in which I have empoyee ID, start date and end date.

 

 

and second data set which i have is a calendar table in which week start date and week end date is mentioned and i have a column which contains the dates on which there was holiday.

 

 

So what I need to find is i have to check each employee independently and check from calendar table and holiday table that, if start date of employee is less than week start date and end date of employee is greater than week end date and also check the condition if there was any holiday in that week or not and I have also to exclude weekend.

lets suppose the employee start date is 1/1/2017 and end date is 1/1/2020 and there was no holiday in first week of 2019, so in first week of 2019 which starts from 1/1/2019 to 1/6/2019 i have to calculate how many days an employee came excluding weekends

 so the result comes out to be 4 days and i have to calculate this for each employee

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    HI Anonymous ,

     

    For my sample, I just extract the 2019 holidays. You could reference it to modify to meet your actual situations. There are two formulas. One is calculating total working days and another one is the measure of weekly working days.

    Calendar =
VAR t =
    CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2019, 12, 31 ) )
RETURN
    ADDCOLUMNS ( t, "Working day", WEEKDAY ( [Date], 2 ) )

    Weekly working days =
VAR Star_Date =
    CALCULATE (
        MIN ( 'Calendar'[Date] ),
        FILTER (
            ALLEXCEPT ( 'Calendar', 'Date'[Period] ),
            'Calendar'[Date] >= MAX ( 'Date'[Date from] )
                && 'Calendar'[Date] <= MAX ( 'Date'[Date to] )
                && 'Calendar'[No-working day] = 0
        )
    )
VAR End_Date =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        FILTER (
            ALLEXCEPT ( 'Calendar', 'Date'[Period] ),
            'Calendar'[Date] >= MAX ( 'Date'[Date from] )
                && 'Calendar'[Date] <= MAX ( 'Date'[Date to] )
                && 'Calendar'[No-working day] = 0
        )
    )
RETURN
    DATEDIFF ( Star_Date, End_Date + 1, DAY )

    Total working days =
VAR Star_Date =
    MAX ( Employees[Start Date] )
VAR End_Date =
    MAX ( Employees[End Date] )
VAR today =
    TODAY ()
VAR diff =
    CALCULATE (
        DATEDIFF ( Star_Date, End_Date, DAY ),
        FILTER ( 'Calendar', 'Calendar'[No-working day] = 0 )
    )
VAR diff_today =
    CALCULATE (
        DATEDIFF ( Star_Date, today, DAY ),
        FILTER ( 'Calendar', 'Calendar'[No-working day] = 0 )
    )
RETURN
    IF (
        End_Date = BLANK (),
        diff_today,
        IF ( End_Date > DATE ( 2019, 1, 1 ), diff )
    )

     

     

2 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    HI Anonymous ,

     

    For my sample, I just extract the 2019 holidays. You could reference it to modify to meet your actual situations. There are two formulas. One is calculating total working days and another one is the measure of weekly working days.

    Calendar =
VAR t =
    CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2019, 12, 31 ) )
RETURN
    ADDCOLUMNS ( t, "Working day", WEEKDAY ( [Date], 2 ) )

    Weekly working days =
VAR Star_Date =
    CALCULATE (
        MIN ( 'Calendar'[Date] ),
        FILTER (
            ALLEXCEPT ( 'Calendar', 'Date'[Period] ),
            'Calendar'[Date] >= MAX ( 'Date'[Date from] )
                && 'Calendar'[Date] <= MAX ( 'Date'[Date to] )
                && 'Calendar'[No-working day] = 0
        )
    )
VAR End_Date =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        FILTER (
            ALLEXCEPT ( 'Calendar', 'Date'[Period] ),
            'Calendar'[Date] >= MAX ( 'Date'[Date from] )
                && 'Calendar'[Date] <= MAX ( 'Date'[Date to] )
                && 'Calendar'[No-working day] = 0
        )
    )
RETURN
    DATEDIFF ( Star_Date, End_Date + 1, DAY )

    Total working days =
VAR Star_Date =
    MAX ( Employees[Start Date] )
VAR End_Date =
    MAX ( Employees[End Date] )
VAR today =
    TODAY ()
VAR diff =
    CALCULATE (
        DATEDIFF ( Star_Date, End_Date, DAY ),
        FILTER ( 'Calendar', 'Calendar'[No-working day] = 0 )
    )
VAR diff_today =
    CALCULATE (
        DATEDIFF ( Star_Date, today, DAY ),
        FILTER ( 'Calendar', 'Calendar'[No-working day] = 0 )
    )
RETURN
    IF (
        End_Date = BLANK (),
        diff_today,
        IF ( End_Date > DATE ( 2019, 1, 1 ), diff )
    )