Only count business days in date diff count

[kattlees]

I need to run a report to see how long it takes something to be done.

I have 

StartDate and EndDate columns and can use a DateDiff(EndDate,StartDate) to get total days it took. 

What I would like to do is be able to NOT count weekend days in the count. So example would be

StartDate               EndDate            ActualTotalDays               TotalDaysWOWeekends

1/31/2018              2/2/2018                     2                                           2

2/2/2018                2/7/2018                    5                                            3  (not counting 2-3 & 2-4)

[Zubair_Muhammad]

HI @kattlees

Try this Column

TotalDaysWOWeekends =
VAR MyDates =
    ADDCOLUMNS (
        GENERATESERIES ( TableName[StartDate] + 1, TableName[EndDate] ),
        "Day", WEEKDAY ( [Value], 2 )
    )
RETURN
    COUNTROWS ( FILTER ( mydates, [Day] <= 5 ) )

[kattlees · Accepted solution]

Does something go in [Value] area?  Here is my formula  and I get "The arguments in GenerateSeries function cannot be blank

TotalDaysWOWeekends =
VAR MyDates =
ADDCOLUMNS (
GENERATESERIES ( V_ACCT[visit_disch_date] + 1, V_ACCT[V_ACCT_HIM_Grouper.ipg_mrcomp_date] ),
"Day", WEEKDAY ( [Value], 2 )
)
RETURN
COUNTROWS ( FILTER ( mydates, [Day] <= 5 ) )

[kattlees]

Thank you so much! This worked perfectly.

[Zubair_Muhammad]

@kattlees

Your formula seems correct

See my file attached here

You could share your file with me

VALUE is the column created by GenerateSeries function

[Zubair_Muhammad]

@kattlees

May be you have some BLANK dates

[Zubair_Muhammad]

@kattlees

Yes I get this error when I make any of the dates is BLANK

See the pic below

[Zubair_Muhammad]

@kattlees