cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kannanAhammed
Helper IV
Helper IV

Count the average

Hi,

 

We have a ticketing system in sharepoint where each day a number of tickets will be submitted and some one in the office will resolve it and send the update (in a nutshell).

I have the date of the first ticket creation. How do i calculate the average ticket perday? can someone suggest a dax function samples.

Also a ticket will be considered aged ticket if it passed 5 days from the creation date. how will i show of aged ticket count.

1 ACCEPTED SOLUTION
kannanAhammed
Helper IV
Helper IV

I was able to get it to work with the below dax function 

CreatDate = date(2020,03,02)
NoOfDays = DATEDIFF([CreatDate],TODAY(),DAY)
AverageTicketPerDay = DIVIDE(COUNT(Tickets[Id]),[NoOfDays])

View solution in original post

3 REPLIES 3
kannanAhammed
Helper IV
Helper IV

I was able to get it to work with the below dax function 

CreatDate = date(2020,03,02)
NoOfDays = DATEDIFF([CreatDate],TODAY(),DAY)
AverageTicketPerDay = DIVIDE(COUNT(Tickets[Id]),[NoOfDays])

View solution in original post

amitchandak
Super User IV
Super User IV

@kannanAhammed ,

You need to have date without time

example column

 

Date = [datetime].date
or
Date = date(year([datetime]),month([datetime]),day([datetime]))

 

 

Then try a measure like

 

averageX(Values(Table[Create Date]), calculate(Count(Table[Ticket ID])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thanks Amitchandak,

I was attemting the below

Measure = AVERAGEX( VALUES(date(2021,03,02)), CALCULATE(COUNT(Tickets[Id])))
gives me an error as "The VALUES function expects a column reference expression or a table reference expression for argument '1'."
 
So I created a new column as below
CreatedDate = date(2021,03,02)
and a measure as below
Measure = AVERAGEX( VALUES(Tickets[CreatedDate]), CALCULATE(COUNT(Tickets[Id])))
 
But it returns the total count 
 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors