Count all tickets that has been closed in a certain month

[DEickhoff]

Hi Everyone,

I'm new here (and also new to PBI) so sorry if this is something that has been asked a thousand times or if this is the wrong place. I'm still figuring out the difference between DAX and PowerQuery.

So, for my first question on this forum:

We use a ticket system and we want to visualise how many tickets have been created and closed. The first one was pretty easy. The second one not so. I created a measure that counts all the tickets were the closing date is not blank. Because, when a ticket is closed, it has a closing date. When its not closed, it doens't.

The problem I have with this is that its only showing the tickets that has been created ánd closed in that month. For example a ticket closed in februari but created in januari won't show up in februari but in januari. So the closed tickets only show in the month they have been created and not in the month they have been closed.

I think it has something to do with a relation because when I disable the createdate relation with the calendar en enable it with the final date, then it also shows tickets from other months that has been closed in that specifiek month. But i've noticed only one relation can be active, so i'm now sure what to do now.

The measure I use (I think its DAX) is this:

closedChanges = 
calculate(
    count( 'changeactivities_2022'[activityNumber] ),
    'changeactivities_2022'[finalDate] <> BLANK ()
)

I hope someone can help me out with this one!

[DEickhoff · Accepted solution]

So I found the issue. There was a active relation between the date table en something completely different. After removing that relation the problem was fixed.

[BrianConnelly]

Are you using a Date Table?  You should and it is a best practice.  

That said, you could have multiple relationships to the table.  However, only one relationship can be active, but we can use the non active relations with the function userelationship.  Your code would then be...

closedChanges = 
calculate(
    count( 'changeactivities_2022'[activityNumber] ),
    'changeactivities_2022'[finalDate] <> BLANK (),
USERELATIONSHIP(<datefromTickettable>,<dateincalenarthatisnotactiverelationship>)  
)

[DEickhoff]

@BrianConnelly thanks for your reaction. I tried but no luck.

I have a date table with a active relationship to the creationDate. Using the Userelationship function doensn't change anything. The only thing that gives me the correct result is if I make a active relationship between the date table and the finalDate. But then yeah, there is no active relationship between the creationDate so that gives me other problems.

Both creationDate and finalDate are coming from the same datasource. Does that matter?

For now, it just seems the userelaltionship function doesnt work.

PS, made a (I think) better way to count the closedChanges, so this is the DAX i'm trying to work with:

# Closed Activities = 
CALCULATE(
    [# Activities],
    changeactivities_2022[Open] = FALSE(),
USERELATIONSHIP(changeactivities_2022[finalDate],'Kalender'[Datum])
)

[BrianConnelly]

Can you post a picture of your model?  Or the relationships you have?

[Greg_Deckler]

@DEickhoff 
Take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[DEickhoff]

@Greg_Deckler thanks for your reply. I'm not that good in PowerBI, i'm still learning, but I don't think that those 2 options are giving me a answer I need. Its about calculating open tickets.

Like I said, if I enable the relation between my calendar and de closed date, it gives me data I need. But that way, the relation between create date and the calendar is disabled. Isn't there a way to enable 2 relations (or more) and if so, do you have a example that matches my DAX i've written in the post?

Maybe those URL's you gave do contain an answer but then again, i'm not that good in PowerBI at the moment.