Counting Open tickets across time to show if we are improving or not

[Anonymous]

Hi

I have a sample of 794 rows of data.  I want to show a line chart across time (month/year) that shows the total number of tickets open at any month.  For example, a ticket created in January but closed in March would show up as a count of 1 in January and February.   Perhaps it would even show in March.... ?  

The chart would look something like this.  You can see things looked bad in May 2022 but due to process improvements we started to improve/close more tickets than were being opened.

Problem: My dax is not producing the results I am expecting.  See the last data point, 108?  I think this should be 93.  You can test it because I have filter boxes to turn on or off.  (ie: not completed N, Include team member Y)

My data looks like this:

I am testing that the

• 'created_at' date is >= the Minimum Date in the X-Axis
• that the 'completed_at' date is <= Maximum Date of the same month or blank if still open.  
• Includes members of a certain team - 'sampledata'[Included Team Member] = "Yes"
•  

The DAX looks like this.

ActionsOpen =

var MaxDate = MAX(Dates[Date])

var MinDate = MIN(Dates[Date])

var Result = CALCULATE(

COUNTROWS(sampledata)

, 'sampledata'[Included Team Member] = "Yes"

,'sampledata'[created_at]<=MaxDate

, OR('sampledata'[completed_at]>=MinDate, ISBLANK('sampledata'[completed_at]) )

, ALL(Dates)

)

RETURN if ( MaxDate <= EOMONTH ( TODAY(), 1 ), Result)

My question to you:

can you help me get my DAX so the line chart correctly shows how many tickets were open at any month?  I trust it if it ends at 93!

thank you in advance.

John.

See linked PBI file with sample data.

[amitchandak]

@Anonymous , refer if current employee of HR Blog or the attached files can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970