Forum Discussion

labuser1235's avatar
labuser1235
Icon for Helper IV rankHelper IV
6 years ago
Solved

Date difference for working days excluding sundays

Hi All,   I would like to created a calculated column where it calculates date difference between two columns and return if it falls under 0-24 hrs or 24-48 hrs or 48-72 hrs or above 72hrs. Excludi...
Need Help
Show and Tell
Tips and Tricks
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi labuser1235 ,

     

    In your scenario, we should create a date table like that.

    date = ADDCOLUMNS(CALENDARAUTO(),"Sunday",IF(WEEKDAY([Date],2) = 7,1,0))

     

    Then we can create a calculated column in our fact table as below.

    Column = 
VAR workingdays =
    CALCULATE (
        COUNTROWS ( 'date' ),
        FILTER (
            'date',
            'date'[Date] > 'Table'[Start Date]
                && 'date'[Date] <= 'Table'[End Date]
                && 'date'[Sunday] <> 1
        )
    )
RETURN
    SWITCH (
        TRUE (),
        workingdays = 1, "0 to 24 Hrs",
        workingdays = 2, "24 to 48 Hrs",
        workingdays = 3, "48 to 72 Hrs",
        "More that 62 Hrs"
    )

     

    For more details, please check the pbix as attached.

     

javirmerino's avatar
javirmerino
Icon for Helper III rankHelper III
6 years ago

I've just realised you want Sundays excluded also. Do you have a Date Dimensions table in use?

 

If so, you can add an additional column, IsWeekDay, using the SWITCH function;

IsWeekDay =
SWITCH (
    WEEKDAY ( [Date] ),
    1, 0,
    1
)

 This basically says if weekday is 1 (Sunday) then 0 (false), else 1 (True), which can be referenced in a second measure;

=
CALCULATE (
    SUM ( DateTable[IsWorkday] ),
    DATESBETWEEN (
        DateTable[Date],
        'Source'[StartDate],
        'Source'[EndDate]
    )
)

Finally, this can be referenced in the original If formula or a new SWITCH to convert the integers into your string for binning.

 

I hope that helps a little more for you.