Forum Discussion

msbutton27's avatar
msbutton27
Regular Visitor
3 years ago
Solved

How to count tickets opened and closed on the same graph

Currently I can show my opened tickets or closed tickets for any month individually, but I would like to be able to combine the two charts together, but having issues. Pre-warming, I am extremely new to PowerBI, so apologies in adavance.

 

Here are my two charts:

 

Actual Data is simliar to below:

 

Current Visualization details:

 

Need Help
  • parry2k's avatar
    parry2k
    3 years ago

    msbutton27 it is pretty straightforward, add a date dimension to your model, see these videos on how to do it, and change the type "create date" and "close date" columns to type date in PQ.

     

    Set the relationship of these two dates with the date dimension on the date column, one relationship will be active, other will be inactive. Assume create date relationship is active, add the following two measures, and in visuals, use year and month from the date dimension and the measures on the values:

     

    Learn basics of Time Intelligence DAX functions - Part 1 - YouTube

     

    Learn why marking a Date dimension as date table is critical to use DAX Time Intelligence functions - YouTube

     

    Create Count = COUNTROWS ( YourTable )

Closed Count = CALCULATE ( [Create Count], USERELATIONSHIP ( YourTable[Closed Date], DateTable[Date] ) )

     

    👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

    If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️

     

     

6 Replies

  • Alex_Sawdo's avatar
    Alex_Sawdo
    Icon for Resolver II rankResolver II
    3 years ago

    I'm not entirely sure what your data model looks like, however I'd recommend creating a seperate calendar table then creating two inactive relationships between the date column of the calendar table and two new date columns from your source table (Closed Date and Open Date). By adding in two columns as dates without the time part, the relationships will work correctly otherwise there may be issues. Once this is done, create two measures seen here:

    Closed = 
CALCULATE(
COUNT(
[Unique ID Field]
),
USERELATIONSHIP(
[Close Date],
'Calendar'[Date]
)
)
    Opened = 
CALCULATE(
COUNT(
[Unique ID Field]
),
USERELATIONSHIP(
[Open Date],
'Calendar'[Date]
)
)

    The top one is for the closed tickets and the bottom one is for opened tickets. 

    • msbutton27's avatar
      msbutton27
      Regular Visitor
      3 years ago

      Thanks - I created a calendar and trying to create the measurement and this happens:

       

      • msbutton27's avatar
        msbutton27
        Regular Visitor
        3 years ago

        I put this in but doesn't work:

        ClosedCount =
        CALCULATE(
        COUNT(
        [Closed]
        ),
        USERELATIONSHIP(RawData[Closed],
        'Calendar'[Date]
        )
        )

         

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

    msbutton27 it is pretty straightforward, add a date dimension to your model, see these videos on how to do it, and change the type "create date" and "close date" columns to type date in PQ.

     

    Set the relationship of these two dates with the date dimension on the date column, one relationship will be active, other will be inactive. Assume create date relationship is active, add the following two measures, and in visuals, use year and month from the date dimension and the measures on the values:

     

    Learn basics of Time Intelligence DAX functions - Part 1 - YouTube

     

    Learn why marking a Date dimension as date table is critical to use DAX Time Intelligence functions - YouTube

     

    Create Count = COUNTROWS ( YourTable )

Closed Count = CALCULATE ( [Create Count], USERELATIONSHIP ( YourTable[Closed Date], DateTable[Date] ) )

     

    👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

    If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️