teddy0bear
New Member
- Joined
- Aug 26, 2016
- Messages
- 4
I'm looking at calculating how many days a ticket has been open and the average days all the tickets have been open for that month and group. I have a date table that is not connected a ticket table
[TABLE="width: 500"]
<tbody>[TR]
[TD]Ticket[/TD]
[TD]Group[/TD]
[TD]Created[/TD]
[TD]CloseDate[/TD]
[/TR]
[TR]
[TD]Broken Screen[/TD]
[TD]West[/TD]
[TD]8/24/2017[/TD]
[TD]4/26/2018[/TD]
[/TR]
[TR]
[TD]Broken Mouse[/TD]
[TD]West[/TD]
[TD]2/4/2018[/TD]
[TD]5/18/2018[/TD]
[/TR]
[TR]
[TD]Broken Laptop[/TD]
[TD]West[/TD]
[TD]3/4/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Broken Brain[/TD]
[TD]East[/TD]
[TD]3/29/2018[/TD]
[TD]5/20/2018[/TD]
[/TR]
</tbody>[/TABLE]
What I'm trying to achieve is:
[TABLE="width: 520"]
<tbody>[TR]
[TD][/TD]
[TD]1/31/2018[/TD]
[TD]2/28/2018[/TD]
[TD]3/31/2018[/TD]
[TD]4/30/2018[/TD]
[TD]5/31/2018[/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD]160[/TD]
[TD]106[/TD]
[TD]99[/TD]
[TD]71[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD] Broken Screen[/TD]
[TD]160[/TD]
[TD]188[/TD]
[TD]219[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Broken Mouse[/TD]
[TD][/TD]
[TD]24[/TD]
[TD]55[/TD]
[TD]85[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Broken Laptop[/TD]
[TD][/TD]
[TD][/TD]
[TD]27[/TD]
[TD]57[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]32[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Broken Brain[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]32[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I'm getting close, I think, I have a DAX measure that will calculate the days open but it's not averaging them per team and I'm not sure how to make it. It's currently returning the last ticket values. Which, sort of makes sense but is not what I want. Can anyone tell what I'm doing wrong or if there is a better way to do this?
The formula I currently have is:
=if(or(max(Ticket[CloseDate])<MAX('Calendar'[Date]), max(Ticket[Created])>max('Calendar'[Date])), blank(), datediff(max('Ticket'[Created]), max('Calendar'[Date]), day))
[TABLE="width: 500"]
<tbody>[TR]
[TD]Ticket[/TD]
[TD]Group[/TD]
[TD]Created[/TD]
[TD]CloseDate[/TD]
[/TR]
[TR]
[TD]Broken Screen[/TD]
[TD]West[/TD]
[TD]8/24/2017[/TD]
[TD]4/26/2018[/TD]
[/TR]
[TR]
[TD]Broken Mouse[/TD]
[TD]West[/TD]
[TD]2/4/2018[/TD]
[TD]5/18/2018[/TD]
[/TR]
[TR]
[TD]Broken Laptop[/TD]
[TD]West[/TD]
[TD]3/4/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Broken Brain[/TD]
[TD]East[/TD]
[TD]3/29/2018[/TD]
[TD]5/20/2018[/TD]
[/TR]
</tbody>[/TABLE]
What I'm trying to achieve is:
[TABLE="width: 520"]
<tbody>[TR]
[TD][/TD]
[TD]1/31/2018[/TD]
[TD]2/28/2018[/TD]
[TD]3/31/2018[/TD]
[TD]4/30/2018[/TD]
[TD]5/31/2018[/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD]160[/TD]
[TD]106[/TD]
[TD]99[/TD]
[TD]71[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD] Broken Screen[/TD]
[TD]160[/TD]
[TD]188[/TD]
[TD]219[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Broken Mouse[/TD]
[TD][/TD]
[TD]24[/TD]
[TD]55[/TD]
[TD]85[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Broken Laptop[/TD]
[TD][/TD]
[TD][/TD]
[TD]27[/TD]
[TD]57[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]32[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Broken Brain[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]32[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I'm getting close, I think, I have a DAX measure that will calculate the days open but it's not averaging them per team and I'm not sure how to make it. It's currently returning the last ticket values. Which, sort of makes sense but is not what I want. Can anyone tell what I'm doing wrong or if there is a better way to do this?
The formula I currently have is:
=if(or(max(Ticket[CloseDate])<MAX('Calendar'[Date]), max(Ticket[Created])>max('Calendar'[Date])), blank(), datediff(max('Ticket'[Created]), max('Calendar'[Date]), day))