Ratio of Counts By Month

[hxjc18]

Hello All!

So, I'm running into some difficulty and hopefully the community can help me out on this one. Right now I'm working on a stats dashboard for our internal help desk and one of the metrics we need to capture is the ratio of tickets opened and closed by month. I've already created a calendar table using a dax formula (see below) and this is the measure I came up with:

Open-Close Ratio =
CALCULATE(
COUNT(ticket_data[Closed])/COUNT(ticket_data[Opened]),
FILTER(ticket_data, ticket_data[Opened] <= max(mycalendar[Date])||ticket_data[Closed]<= max(mycalendar[Date])), FILTER(ticket_data, ticket_data[Opened] >= min(mycalendar[Date])||ticket_data[Closed]>= min(mycalendar[Date])))

And the graph:

I'm running into two issues: 1) how do I summarize the data by month and 2) although I know I'm not summarizing by month, I'm not convinced that they way I'm doing the filtering is correct.

Any assistance would be much appreciated!

-JC

The dax equation I'm using to create the calendar:

mycalendar =
GENERATE (
CALENDAR( DATE( YEAR( TODAY() ) - 2, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),
VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday
VAR currentDay = [Date]
VAR days = DAY( currentDay )
VAR months = MONTH ( currentDay )
VAR years = YEAR ( currentDay )
VAR nowYear = YEAR( TODAY() )
VAR nowMonth = MONTH( TODAY() )
VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1
VAR todayNum = WEEKDAY( TODAY() )
VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )
RETURN ROW (
"day", days,
"month", months,
"year", years,
"day index", dayIndex,
"week index", weekIndex,
"month index", INT( (years - nowYear ) * 12 + months - nowMonth ),
"year index", INT( years - nowYear )
)
)

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Please share the expected result in a Table.  Once the values in the Table are correct, you can create any visual you want.