cancel
Showing results for
Did you mean:
Helper I

## How to create date entries for how long a Ticket has been open?

Hi. I have a dataset that contains information on our Tickets in the IT department. I have a created date, closed date and amount of days the ticket has been open.

My issue is that we want a bar chart showing each day how many tickets have been open for different time groups:

0-2 days = green

3-7 = yellow

8-30 = red

31+ = black

However, the chart only displays the ticket for the created date. Please see the below example. The Black section of the chart shows 1 ticket that has been open for 37 days and was created on the 15/01. Therefore, the same Black section should show on each bar (the pink underlined bars) from 15/01 to the 22/02. How would I achieve this? Thank you:

1 ACCEPTED SOLUTION
Community Support

Hi @GeorgeG

I think you have a bar chart and show the count of the value which create date and close date are all in range.

Ex: 1 ticket that has been open for 37 days and was created on the 2021/01/15 and closed on the 2021/02/22. Therefore, the same Black section should show on each bar from 2021/01/15 to the 2021/02/22.

My Sample:

Day Open and Flag are all calculated columns.

``DayOpen = DATEDIFF('Table'[CreateDate],'Table'[CloseDare],DAY)``
``````Flag =
SWITCH (
TRUE (),
'Table'[DayOpen] <= 2, "0-2",
'Table'[DayOpen] <= 7, "3-7",
'Table'[DayOpen] <= 30, "8-30",
"31+"
)``````

Build an unrelated date table by dax.

``Date = CALENDAR(MIN('Table'[CreateDate]),MAX('Table'[CloseDare]))``

Then build a count measure as below.

``````Count =
IF(
MAX('Table'[CreateDate])<=MAX('Date'[Date])&&MAX('Table'[CloseDare])>=MAX('Date'[Date]),
CALCULATE(COUNT('Table'[Flag]),FILTER(ALL('Table'),'Table'[Flag]=MAX('Table'[Flag])&&'Table'[CreateDate]<=MAX('Date'[Date])&&'Table'[CloseDare]>=MAX('Date'[Date]))),
0)``````

Build a bar chart.

You see 31+ has two, one is between 2021/01/13 to 2021/02/20 and another is between 2021/01/15 to 2021/02/22.

Select Blank bar in 2020/2/10

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi @GeorgeG

I think you have a bar chart and show the count of the value which create date and close date are all in range.

Ex: 1 ticket that has been open for 37 days and was created on the 2021/01/15 and closed on the 2021/02/22. Therefore, the same Black section should show on each bar from 2021/01/15 to the 2021/02/22.

My Sample:

Day Open and Flag are all calculated columns.

``DayOpen = DATEDIFF('Table'[CreateDate],'Table'[CloseDare],DAY)``
``````Flag =
SWITCH (
TRUE (),
'Table'[DayOpen] <= 2, "0-2",
'Table'[DayOpen] <= 7, "3-7",
'Table'[DayOpen] <= 30, "8-30",
"31+"
)``````

Build an unrelated date table by dax.

``Date = CALENDAR(MIN('Table'[CreateDate]),MAX('Table'[CloseDare]))``

Then build a count measure as below.

``````Count =
IF(
MAX('Table'[CreateDate])<=MAX('Date'[Date])&&MAX('Table'[CloseDare])>=MAX('Date'[Date]),
CALCULATE(COUNT('Table'[Flag]),FILTER(ALL('Table'),'Table'[Flag]=MAX('Table'[Flag])&&'Table'[CreateDate]<=MAX('Date'[Date])&&'Table'[CloseDare]>=MAX('Date'[Date]))),
0)``````

Build a bar chart.

You see 31+ has two, one is between 2021/01/13 to 2021/02/20 and another is between 2021/01/15 to 2021/02/22.

Select Blank bar in 2020/2/10

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements