cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
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])
3 REPLIES 3
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])
Super User

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

Helper IV

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors