Calculate solve complaints days without Weekends

[Anonymous]

@amitchandak @Jimmy801 this might be a common issue but I cannot find an exact solution, Please help me if you can, Thanks in advance. 

I am trying to calculate the number of days we are taking to solve a case excluding the weekends.

here are the values:

Table = Incident 

Column = Created date  

Column = Solve date  

Column = Weekdays Number           Value = Saturday and Sunday = 6 and 7

Column = IsworkingDay                    Value = True and False

[Anonymous]

you can view the file with sample data 

https://1drv.ms/u/s!Aj7YLBYfrzPkihemTZdczXX5R9Ns?e=bZDTni

[Jimmy801]

Hello @Anonymous 

it was offered a solution in DAX. If you would need a solution in Power Query, let me know

BR

Jimmy

[Anonymous]

Hello @Jimmy801 Yes it would be a great help if you can share the solution. Thanks.

[Jimmy801]

Hello @Anonymous 

here a Power query solution. Add a new column with this formula

List.Count(List.Select(List.Dates([#"Created date "],Duration.TotalDays([#"Solve date"]-[#"Created date "])+1,#duration(1,0,0,0)), each Date.DayOfWeek(_)<5))

here a complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLQMzDUMzJU0lEyMocyY2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Created date " = _t, #"Solve date  " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Created date ", type date}, {"Solve date  ", type date}}, "de-DE"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Working Days", each List.Count(List.Select(List.Dates([#"Created date "],Duration.TotalDays([#"Solve date  "]-[#"Created date "])+1,#duration(1,0,0,0)), each Date.DayOfWeek(_)<5)), type number)
in
    #"Added Custom"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[littlemojopuppy]

Hi @Anonymous 

Try this measure...

Weekdays to Solve :=
COUNTROWS(
	FILTER(
		ALL(DateTable),
		DateTable[Date] >= SELECTEDVALUE(CreatedDate) &&
		DateTable[Date] <= SELECTEDVALUE(SolveDate) &&
		NOT(DateTable[Weekday] IN {6, 7}) &&
		DateTable[IsWorkingDay] = TRUE()
	)
)

Hope this helps!  🙂

[Anonymous]

@littlemojopuppy Thanks for your prompt response I tried the same DAX but i am getting the following error any suggestion on this?

[littlemojopuppy]

@Anonymous looks like you're missing a comma after ALL()

[Anonymous]

after including the comma still getting the error

[littlemojopuppy]

Can you provide the actual DAX you're trying and not a picture of it?

[Anonymous]

solved days3 = COUNTROWS(ALL(incidents), incidents[Createdon Date] >=SELECTEDVALUE(incidents[createdon]) && incidents[Createdon Date] <=SELECTEDVALUE(incidents[cmx_solveddate]) && NOT(incidents[weekdaysnumber] in {6,7}) && incidents[IsworkingDay] = TRUE()))

[littlemojopuppy]

You have an extra closing parenthesis at the end

[Anonymous]

Now i am getting the error" too many arguments passed to the countrows function"

solved days3 = COUNTROWS(ALL(incidents),incidents[Createdon Date] >=SELECTEDVALUE(incidents[Createdon Date]) && incidents[createdon] <=SELECTEDVALUE(incidents[cmx_solveddate]) && NOT(incidents[weekdaysnumber] in {6,7}) && incidents[IsworkingDay] = TRUE())

[littlemojopuppy]

Please copy it exactly as posted in the thread above

[Anonymous]

Thanks for your help, but now the column is blank there is no values.

olved days3 = COUNTROWS(FILTER(ALL(incidents),incidents[Createdon Date] >=SELECTEDVALUE(incidents[Createdon Date]) && incidents[createdon] <=SELECTEDVALUE(incidents[cmx_solveddate]) && NOT(incidents[weekdaysnumber] in {6,7}) && incidents[IsworkingDay] = TRUE()))

[littlemojopuppy]

You should create that as a measure - not as a calculated column.  Generally speaking, calculated columns should be avoided as much as possible.

[Anonymous]

It is still showing as blank, 

Measure = COUNTROWS(FILTER(ALL(incidents),incidents[Createdon Date] >=SELECTEDVALUE(incidents[Createdon Date]) && incidents[Createdon Date] <=SELECTEDVALUE(incidents[cmx_solveddate]) && NOT(incidents[weekdaysnumber] in {6,7}) && incidents[IsworkingDay] = TRUE()))

[littlemojopuppy]

@Anonymous you can't show that measure in a card.  It needs to be able to have some kind of context to be able to look at your incidents to determine number of days from creation to completion.  Try using it in a matrix or table.

[littlemojopuppy · Accepted solution]

@Anonymous if you want to try using something in a card try this.

VAR	IncidentSummary =
	SUMMARIZE(
		ALLSELECTED(Incidents),
		Incidents[IncidentID],
		Incidents[Createdon Date],
		Incidents[cmx_solveddate],
		"DaysToResolve",
		COUNTROWS(
			FILTER(
				ALL(DateTable),
				DateTable[Date] >= Incidents[Createdon Date] &&
				DateTable[Date] <= Incidents[cmx_solveddate] &&
				NOT(DateTable[Weekday] IN {6, 7}) &&
				DateTable[IsWorkingDay] = TRUE()
			)
		)
	)
RETURN

AVERAGEX(
	IncidentSummary,
	[DaysToResolve]
)

I'm typing this in Notepad so no warranty it's correct.  And I expect you to make a reasonable effort to debug for extra parentheses and that it was copied correctly.  If it's not good after that, I need you to provide actual data I can work with.

[littlemojopuppy]

@Anonymous Nothing?

[Anonymous]

@littlemojopuppy Appology for the delay. Appriciate your help