Tickets Opened and Closed per Month With Graph

[RShackelford]

What I need to do: 

Create a bar chart that shows the number of tickets opened and closed each month. 

This is a fairly simple task but I'm at sock puppet level with DAX and I'm really overthinking this. I assume I need to create a date table and link the open and closed fields? 

So from the data table I should be able get:

	Tickets Opened	Tickets Closed
November	7	4
December	3	4

Sample Data:

Ticket Num	Opened	Closed	State
106206	11/1/2020	11/28/2020	Closed
109081	11/15/2020	11/28/2020	Closed
108866	12/1/2020		Open
100469	11/1/2020	12/15/2020	Closed
103556	11/15/2020	11/28/2020	Closed
108243	12/1/2020	12/15/2020	Closed
105876	12/1/2020		Open
102317	11/1/2020	12/15/2020	Closed
104029	11/15/2020	11/28/2020	Closed
100450	11/1/2020	12/15/2020	Closed

Thanks

[MattAllington · Accepted solution]

Yes, get a calendar table with a month column, year column and date column (minimum). Create and active relationship to the opened column and an inactive relationship to the closed column. 
Write measures

Total opened = countrows(Data)

total closed = calculate([total opened],userelationship(calendar[date],data[closed date]))

[littlemojopuppy · Accepted solution]

Hi @RShackelford 

You want to create a date table and mark it accordingly.  Then you will create two relationships between the date table and your tickets data.  One between DateTable[Date] and Tickets[Opened] and the second between DateTable[Date] and Tickets[Closed].  Your data model should look something like this.

Then you need two measures

Tickets Opened:=CALCULATE(
		DISTINCTCOUNT(Tickets[Ticket Num]),
		USERELATIONSHIP('Calendar'[Date], Tickets[Opened])
	)

Tickets Closed:=CALCULATE(
		DISTINCTCOUNT(Tickets[Ticket Num]),
		USERELATIONSHIP('Calendar'[Date], Tickets[Closed])
	)

The output looks like this

[MattAllington · Accepted solution]

Yes, get a calendar table with a month column, year column and date column (minimum). Create and active relationship to the opened column and an inactive relationship to the closed column. 
Write measures

Total opened = countrows(Data)

total closed = calculate([total opened],userelationship(calendar[date],data[closed date]))

[RShackelford]

Thanks @MattAllington. That works. 

BTW, I just ordered your book before I posted this question. Small PBI world.