Forum Discussion

RShackelford's avatar
RShackelford
Frequent Visitor
5 years ago
Solved

Tickets Opened and Closed per Month With Graph

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 OpenedTickets Closed
November74
December34

 

 
 

 

Sample Data:

Ticket NumOpenedClosedState
10620611/1/202011/28/2020Closed
10908111/15/202011/28/2020Closed
10886612/1/2020 Open
10046911/1/202012/15/2020Closed
10355611/15/202011/28/2020Closed
10824312/1/202012/15/2020Closed
10587612/1/2020 Open
10231711/1/202012/15/2020Closed
10402911/15/202011/28/2020Closed
10045011/1/202012/15/2020Closed

 

 

Thanks

DAX Commands and Tips
  • MattAllington's avatar
    MattAllington
    5 years ago

    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's avatar
    littlemojopuppy
    5 years ago

    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

3 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      RShackelford
      Frequent Visitor
      5 years ago

      Thanks MattAllington. That works. 

       

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

  • littlemojopuppy's avatar
    littlemojopuppy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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