cancel
Showing results for
Did you mean:
Member

## Only count business days in date diff count

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)

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: Only count business days in date diff count

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

TotalDaysWOWeekends =
VAR MyDates =
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 ) )

7 REPLIES 7
Super User

## Re: Only count business days in date diff count

Try this Column

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

Member

## Re: Only count business days in date diff count

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

TotalDaysWOWeekends =
VAR MyDates =
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 ) )

Super User

## Re: Only count business days in date diff count

@kattlees

See my file attached here

You could share your file with me

VALUE is the column created by GenerateSeries function

Super User

## Re: Only count business days in date diff count

@kattlees

May be you have some BLANK dates

Super User

## Re: Only count business days in date diff count

@kattlees

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

See the pic below

Member

## Re: Only count business days in date diff count

Thank you so much! This worked perfectly.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 298 members 3,020 guests
Recent signins: