topic Re: Tickets Opened and Closed per Month With Graph in DAX Commands and Tips

Tickets Opened and Closed per Month With Graph

RShackelford — Tue, 29 Dec 2020 21:28:47 GMT

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

Re: Tickets Opened and Closed per Month With Graph

MattAllington — Tue, 29 Dec 2020 21:52:37 GMT

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]))

Re: Tickets Opened and Closed per Month With Graph

littlemojopuppy — Tue, 29 Dec 2020 22:26:50 GMT

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

Re: Tickets Opened and Closed per Month With Graph

RShackelford — Tue, 29 Dec 2020 22:57:07 GMT

Thanks MattAllington. That works.

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