Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate solve complaints days without Weekends

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 sol...
  • littlemojopuppy's avatar
    littlemojopuppy
    5 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

Jimmy801's avatar
Jimmy801
Icon for Community Champion rankCommunity Champion
5 years ago

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