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 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

 

  • 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.

21 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

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

       

      • littlemojopuppy's avatar
        littlemojopuppy
        Icon for Community Champion rankCommunity Champion
        5 years ago

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

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

    Hello Anonymous 

     

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

     

    BR

     

    Jimmy

    • 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