cancel
Showing results for
Did you mean:
Helper I

## Deed a Dax for Average Number of Months a Ticket is Open, Within a Quarter

Hi!
I need to calculate an average number of months a ticket is open and dispay this average on a line chart, per each quarter.
Below is a sample dummy dataset with the columns I need to operate on.

The "months ticket open" dax calculation should always depend on the status of the ticket.
If status is "Open" then the "Months Ticket Open" shoupd be calculated by taking the difference in months between Open_Date and Quarter_End_Date.
If status is "Closed" then the "Months Ticklet Open" shoupd be calculated by taking the difference in months between Open_Date and Closed_Date.
Once the "Months Ticket Open" value is obtaned, I need to calculate an "Average Months Ticket Open" value per each Quarter and display it in a chart (each Quarter is defined by Quarter_End_Date column).

Thank You!

1 ACCEPTED SOLUTION
Helper I

I described the logic above, but as I set to describe it in more details, I accidentally solved it myself! I realized that what I needed really is an iterator function that would "wrap" my "datediff" logic. So, here is the key function that solved my issue:

Months Ticket Open =
SUMX (
'Table',
IF (
'Table'[Ticket_Status] = "Closed",
DATEDIFF ( 'Table'[Ticket_Open Date], 'Table'[Ticket_Closed Date], MONTH ), --- Status is Closed
DATEDIFF ( 'Table'[Ticket_Open Date], 'Table'[Quarter_End_Date], MONTH ) --- Status is Open
)
)

In order to calculate the Average, I also ended up using AVERAGEX iterator:
AVG Months Ticket Open =
AVERAGEX (
'Table',
IF (
'Table'[Ticket_Status] = "Closed",
DATEDIFF ( 'Table'[Ticket_Open Date], 'Table'[Ticket_Closed Date], MONTH ),
DATEDIFF ( 'Table'[Ticket_Open Date], 'Table'[Quarter_End_Date], MONTH )
)
)

And next I just took the average

4 REPLIES 4
Super User

@E12345 Going to be a variation of this: Open Tickets - Microsoft Fabric Community

If you can post sample data as text can potentially be more specific.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper I

I have seen people inserting sample PBIX files (with sample data), but I do not see any such attachment options here. Can you direct me to one? Maybe past a screenshot on where to click to add the attachment... So strange that I cannot do it and others can.

Update: Apparently some new members do not have rights to upload files. So, it seems that I do not have the rights to upload a PBIX file.
But I can paste a copy of the dummy data below, which you can copy into an Excel file and upload into your PBIX (?):

 Quarter_End_Date Ticket_No Ticket_Status Open Date Closed Date 3/1/2023 1 Open 1/1/2000 3/1/2023 2 Closed 1/2/2000 2/4/2023 3/1/2023 3 Closed 1/3/2000 2/5/2023 3/1/2023 4 Open 1/4/2000 3/1/2023 5 Open 1/5/2012 3/1/2023 6 Closed 1/6/2000 2/8/2023 3/1/2023 7 Open 1/7/2000 3/1/2023 8 Open 1/8/2000 12/31/2022 9 Open 1/9/2000 12/31/2022 10 Closed 1/10/2000 2/12/2023 12/31/2022 11 Open 1/11/2000 12/31/2022 12 Closed 1/12/2018 2/14/2023 12/31/2022 13 Closed 1/13/2000 2/15/2023 12/31/2022 14 Open 1/14/2000 12/31/2022 15 Open 1/15/1978 12/31/2022 16 Open 1/16/2000 12/31/2022 17 Open 5/17/1999 12/31/2022 18 Closed 1/18/2000 2/20/2023 9/30/2022 19 Open 1/19/2000 9/30/2022 20 Open 1/20/2000 9/30/2022 21 Closed 1/21/2000 2/23/2023 9/30/2022 22 Open 1/22/2000 9/30/2022 23 Open 1/23/2000 9/30/2022 24 Open 3/24/2000 9/30/2022 25 Open 1/25/2000 9/30/2022 26 Open 1/26/2000 6/30/2022 27 Closed 1/27/2000 3/1/2023 6/30/2022 28 Open 1/28/2000 6/30/2022 29 Open 1/29/2000 6/30/2022 30 Closed 1/30/2000 3/4/2023 6/30/2022 31 Open 1/31/2000 6/30/2022 32 Open 2/1/2000 6/30/2022 33 Open 2/2/1995 6/30/2022 34 Closed 2/3/2000 3/8/2023 6/30/2022 35 Open 2/4/2021 6/30/2022 36 Open 2/5/2000 6/30/2022 37 Open 2/6/2016 3/31/2022 38 Open 2/7/2000 3/31/2022 39 Open 2/8/2013 3/31/2022 40 Open 2/9/2000 3/31/2022 41 Closed 2/10/2005 3/15/2023 3/31/2022 42 Closed 2/11/2012 3/16/2023 3/31/2022 43 Closed 2/12/2000 3/17/2021 3/31/2022 44 Open 2/13/2022 3/31/2022 45 Closed 12/14/2021 3/19/2023 3/31/2022 46 Open 2/15/2000
Super User

what's the expected output based on the sample data you provided? and what's the calculation logic?

Proud to be a Super User!

Helper I

I described the logic above, but as I set to describe it in more details, I accidentally solved it myself! I realized that what I needed really is an iterator function that would "wrap" my "datediff" logic. So, here is the key function that solved my issue:

Months Ticket Open =
SUMX (
'Table',
IF (
'Table'[Ticket_Status] = "Closed",
DATEDIFF ( 'Table'[Ticket_Open Date], 'Table'[Ticket_Closed Date], MONTH ), --- Status is Closed
DATEDIFF ( 'Table'[Ticket_Open Date], 'Table'[Quarter_End_Date], MONTH ) --- Status is Open
)
)

In order to calculate the Average, I also ended up using AVERAGEX iterator:
AVG Months Ticket Open =
AVERAGEX (
'Table',
IF (
'Table'[Ticket_Status] = "Closed",
DATEDIFF ( 'Table'[Ticket_Open Date], 'Table'[Ticket_Closed Date], MONTH ),
DATEDIFF ( 'Table'[Ticket_Open Date], 'Table'[Quarter_End_Date], MONTH )
)
)

And next I just took the average

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors