How to count tickets opened and closed on the same graph

[msbutton27]

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:

[parry2k]

@msbutton27 are you getting any error or what? Simply saying not working doesn't explain anything?

[parry2k · Accepted solution]

@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 ...

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! ❤️

[Alex_Sawdo]

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]

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

[msbutton27]

I put this in but doesn't work:

ClosedCount =

CALCULATE(

COUNT(

[Closed]

),

USERELATIONSHIP(RawData[Closed],

'Calendar'[Date]

)

)

[msbutton27]