topic Re: Calculate solve complaints days without Weekends in DAX Commands and Tips

Calculate solve complaints days without Weekends

Anonymous — Wed, 20 Jan 2021 23:15:06 GMT

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

Re: Calculate solve complaints days without Weekends

littlemojopuppy — Wed, 20 Jan 2021 23:27:16 GMT

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

Re: Calculate solve complaints days without Weekends

Anonymous — Thu, 21 Jan 2021 00:04:13 GMT

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

Re: Calculate solve complaints days without Weekends

littlemojopuppy — Thu, 21 Jan 2021 00:34:51 GMT

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

Re: Calculate solve complaints days without Weekends

Jimmy801 — Thu, 21 Jan 2021 07:53:03 GMT

Hello Anonymous

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

Jimmy

Re: Calculate solve complaints days without Weekends

Anonymous — Thu, 21 Jan 2021 14:43:41 GMT

after including the comma still getting the error

Re: Calculate solve complaints days without Weekends

Anonymous — Thu, 21 Jan 2021 14:45:41 GMT

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

Re: Calculate solve complaints days without Weekends

littlemojopuppy — Thu, 21 Jan 2021 14:47:06 GMT

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

Re: Calculate solve complaints days without Weekends

Anonymous — Thu, 21 Jan 2021 15:06:19 GMT

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

Re: Calculate solve complaints days without Weekends

littlemojopuppy — Thu, 21 Jan 2021 15:08:30 GMT

You have an extra closing parenthesis at the end

Re: Calculate solve complaints days without Weekends

Anonymous — Thu, 21 Jan 2021 15:15:23 GMT

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

Re: Calculate solve complaints days without Weekends

littlemojopuppy — Thu, 21 Jan 2021 15:17:46 GMT

Please copy it exactly as posted in the thread above

Re: Calculate solve complaints days without Weekends

Anonymous — Thu, 21 Jan 2021 15:22:43 GMT

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

Re: Calculate solve complaints days without Weekends

littlemojopuppy — Thu, 21 Jan 2021 15:35:46 GMT

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

Re: Calculate solve complaints days without Weekends

Anonymous — Thu, 21 Jan 2021 15:42:09 GMT

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

Re: Calculate solve complaints days without Weekends

Anonymous — Thu, 21 Jan 2021 23:40:31 GMT

you can view the file with sample data

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

Re: Calculate solve complaints days without Weekends

littlemojopuppy — Sat, 23 Jan 2021 01:05:59 GMT

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.

Re: Calculate solve complaints days without Weekends

littlemojopuppy — Sat, 23 Jan 2021 01:11:22 GMT

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.

Re: Calculate solve complaints days without Weekends

Jimmy801 — Tue, 26 Jan 2021 09:50:36 GMT

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

Re: Calculate solve complaints days without Weekends

littlemojopuppy — Thu, 28 Jan 2021 03:07:54 GMT

Anonymous Nothing?