Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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.

Data Protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous ,

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

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