Forum Discussion
Post PatronOnly 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)
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 ) )
7 Replies
- Zubair_Muhammad
Community Champion
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 ) )- Zubair_Muhammad
kattleesDoes 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 ) )- Zubair_Muhammad
Your formula seems correct
You could share your file with me
VALUE is the column created by GenerateSeries function
