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

[E12345]

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!

[Greg_Deckler]

@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.

[E12345]

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

[ryan_mayu]

@E12345 

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

[E12345 · Accepted solution]

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