cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Micha3l
Frequent Visitor

Count days open ticket

Hi All,

 

I'm currently struggeling with calculating the open tickets per day and I feel like I am overcomplicating things. 

The simplified dataset is as following:

IDCreateClose
ID12020-01-182020-01-25
ID22020-01-192020-01-26

 

Furthermore I have a calendar table that is connected to the close date.

I want to create a linechart that shows the amount of tickets open on a particular day during the years.

 

so this is what it probably has to look like:

 

DateOpen
2020-01-170
2020-01-181
2020-01-192
2020-01-202
2020-01-212
2020-01-222
2020-01-232
2020-01-242
2020-01-252
2020-01-261
2020-01-270
2020-01-280

 

This is the measure that I used, but it is only showing the 25th and 26th of January (both 1 result).

 

Backlog =
VAR CurrentDate = MAX(DateTable[Date])
VAR Result =
CALCULATE(
COUNTROWS(FactTable),
ALL(DateTable),
DateTable[Date] <= CurrentDate
)
RETURN
Result

 

I also found similar posts that had sort of the same questions, but I can't get it to work.

 

Regards,

Michael

1 ACCEPTED SOLUTION
Micha3l
Frequent Visitor

Hi All,

 

I found my solution in this post:

https://community.powerbi.com/t5/Desktop/Count-open-support-case/m-p/1085834#M502883

 

What I did was a one to many relationship between my date (1) and facttable (many) and used the following DAX:


Open Cases =
CALCULATE (
COUNTROWS ( FactTable ),
FILTER (
ALL ( FactTable ),
FactTable[Create] <= SELECTEDVALUE ( tbl_dCalendarTable[Date] )
&& (
FactTable[Close] >= SELECTEDVALUE ( tbl_dCalendarTable[Date] )
|| ISBLANK ( FactTable[Close] )
)
)
)

 

Many thanks for the support guys!

 

Regards,

Michael

View solution in original post

8 REPLIES 8
Micha3l
Frequent Visitor

Hi All,

 

I found my solution in this post:

https://community.powerbi.com/t5/Desktop/Count-open-support-case/m-p/1085834#M502883

 

What I did was a one to many relationship between my date (1) and facttable (many) and used the following DAX:


Open Cases =
CALCULATE (
COUNTROWS ( FactTable ),
FILTER (
ALL ( FactTable ),
FactTable[Create] <= SELECTEDVALUE ( tbl_dCalendarTable[Date] )
&& (
FactTable[Close] >= SELECTEDVALUE ( tbl_dCalendarTable[Date] )
|| ISBLANK ( FactTable[Close] )
)
)
)

 

Many thanks for the support guys!

 

Regards,

Michael

View solution in original post

TMOCostanzo
Helper V
Helper V

I don't know if this is overkill or not....

I create a new 'closed' column that is equal to the actual close date or the current date. Then:

#"Added Reporing Dates" = Table.AddColumn(#"LastStep", "DayByDay", each { Number.From([TaskOpen_dt])..Number.From([TaskCloseOrCurrent_dt])}),
#"Expanded To Day By Day" = Table.ExpandListColumn(#"Added Reporing Dates", "DayByDay")


You will get a row for each date the ticket is open, grouping by the DayByDay column and counting the tickets may do it. 

pranit828
Super User II
Super User II

HI @Micha3l 

Use DATEDIFF(create,close,day) 

to get the difference in days.

 

Create a relationship on both tables and choose date column from calander table on axis of the chart.




PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

Hi!

I already have an active relationship between de datetable and the closedate, but not sure how I should use the datediff.

darentengmfs
Super User I
Super User I

@Micha3l 

 

If you just want to create a line chart with a count of open tickets, I suggest that you reference/duplicate the table with the data. Then, you create a new relationship connecting the new table with the date table using the Created Date. Do the count on the new table instead of your current table and use that in your chart.

Hi @darentengmfs 

 

Thanks for the quick reply.

I want to avoid duplicating the table, because it is a facttable that contains more than a million rows.

 

Referencing would also be possible I suppose with userelationship, but can you help me a bit with the code?

 

@Micha3l 

 

Go to Power Query, right click on your current query and choose Reference.

 

Then, select your Created Date column, right click, and choose Remove Other Columns. Then under Transform tab, click Group By, do a count, and define your new column name. It will give you a count of the rows with the same date. You can then define the relationship using the Created Column and use the count as your value.

Hi,

 

if I understand you correctly this wil give me a count of the create dates, but when an ID is open for more than one day, the count will be incorrect. It has to count until the closedate. 

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.