cancel
Showing results for
Did you mean:
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:

 ID Create Close ID1 2020-01-18 2020-01-25 ID2 2020-01-19 2020-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:

 Date Open 2020-01-17 0 2020-01-18 1 2020-01-19 2 2020-01-20 2 2020-01-21 2 2020-01-22 2 2020-01-23 2 2020-01-24 2 2020-01-25 2 2020-01-26 1 2020-01-27 0 2020-01-28 0

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

8 REPLIES 8
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

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.

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.

Frequent Visitor

Hi!

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

Super User I

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.

Frequent Visitor

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?

Super User I

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.

Frequent Visitor

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.

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks