Cumulative count of ticket

[rakeshkumar1890]

Hello friends,

Need your help to visualise line graph for closed and open tickets,

Id	Created	Resolved	Status
132	5/6/2025	5/13/2025	Done
133	5/6/2025		Open
134	5/6/2025	5/21/2025	Done
135	5/6/2025		Open
136	5/6/2025	6/18/2025	Done
137	5/6/2025	5/6/2025	Done
138	5/7/2025	5/17/2025	Done
139	5/7/2025		Open
140	5/7/2025		Open
141	5/7/2025	6/18/2025	Done
142	5/7/2025	6/16/2025	Done
143	5/7/2025	6/16/2025	Done
145	5/7/2025	5/8/2025	Done
146	5/7/2025		Open

Need to calculate cumulative count of Id for status Open and Done, consider with below logic.
Importent thing is that - 
Ex - Id : 132 created on 5/6/2025  and resloved 5/13/2025, hence line graph for this ticket should be in two place
5/6/2025 to 5/12/2025 line should shown in Open on cumalative count
5/13/2025 to till date. line should shown in Done on cumalative count

Thanks

[V-yubandi-msft]

Hi @rakeshkumar1890 ,

Could you confirm if your issue is resolved, or if you need any additional details.

Thank you.

[V-yubandi-msft]

Hi @rakeshkumar1890 ,

Have you been able to review @ryan_mayu response? Does it meet your needs, or would you prefer any adjustments? Let me know if you require anything else.

Thanks.

[V-yubandi-msft]

Hi @rakeshkumar1890 ,

Could you confirm whether @ryan_mayu  response meets your needs, or if any adjustments are required ? Please let us know if you have any additional questions.

Thank you.

[MohamedFowzan1]

Hi @rakeshkumar1890 

Duplicate the table, in each of the table expand the lists for Open in One and Done in the other
For OpenDates table,
= Table.AddColumn(PreviousStep, "OpenDates", each
let
start = [Created],
endOpen = if [Resolved Date] <> null then Date.AddDays([Resolved Date], -1) else DateTime.Date(DateTime.LocalNow()),
list = List.Dates(start, Duration.Days(endOpen - start) + 1, #duration(1,0,0,0))
in
list)

For Done Dates table,
= Table.AddColumn(PreviousStep, "DoneDates", each
if [Resolved Date] <> null then
List.Dates([Resolved Date], Duration.Days(DateTime.Date(DateTime.LocalNow()) - [Resolved Date]) + 1, #duration(1,0,0,0))
else
{}
)

Then append both these tables as a new table, remove un necessary columns, for Status just do this:
if [Open Status] = "Open" then "Open" else "Done"
and have one status column

Use the below measures:

Cumulative Open Tickets =
CALCULATE(
DISTINCTCOUNT('Combined'[Id]),
FILTER(
ALL('Combined'),
'Combined'[Date] <= MAX('Combined'[Date]) &&
'Combined'[Status] = "Open"
)
)

Cumulative Done Tickets =
CALCULATE(
DISTINCTCOUNT('Combined'[Id]),
FILTER(
ALL('Combined'),
'Combined'[Date] <= MAX('Combined'[Date]) &&
'Combined'[Status] = "Done"
)
)

Let me know if this works.

[ryan_mayu · Accepted solution]

@rakeshkumar1890 

you can create a date table and try to create two measure

open = CALCULATE(countrows('Table'),FILTER('Table','Table'[Created]<=max('date'[Date])&&('Table'[Resolved]>=max('date'[Date])||ISBLANK('Table'[Resolved]))))

Done = CALCULATE(countrows('Table'),FILTER('Table','Table'[Resolved]<=max('date'[Date])&&not(ISBLANK('Table'[Resolved]))))

pls see the attachment below

[rakeshkumar1890]

Apologies to late reply,  Many thanks

[burakkaragoz]

Hi @rakeshkumar1890 ,

You want to track how many tickets are open vs done over time, with tickets moving from the open line to the done line when they get resolved.

The logic:

• Ticket created 5/6, resolved 5/13
• From 5/6 to 5/12: counts as "open"
• From 5/13 onwards: counts as "done"

Solution:

Create a date table:

DateTable = CALENDAR(MIN(Tickets[Created]), TODAY())

Cumulative Open measure:

Cumulative Open = 
VAR CurrentDate = MAX(DateTable[Date])
RETURN
SUMX(
    Tickets,
    IF(
        Tickets[Created] <= CurrentDate && 
        (ISBLANK(Tickets[Resolved]) || Tickets[Resolved] > CurrentDate),
        1, 0
    )
)

Cumulative Done measure:

Cumulative Done = 
VAR CurrentDate = MAX(DateTable[Date])
RETURN
SUMX(
    Tickets,
    IF(
        NOT(ISBLANK(Tickets[Resolved])) && Tickets[Resolved] <= CurrentDate,
        1, 0
    )
)

In your line chart:

• X-axis: DateTable[Date]
• Y-axis: Both measures
• You'll get two lines showing running totals

What happens: Each day shows how many tickets are currently open vs done. When a ticket gets resolved, it stops counting in "open" and starts counting in "done". So your lines will show the actual status progression over time.

For ticket 132: open line goes up on 5/6, done line goes up on 5/13.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[rohit1991]

Hi @rakeshkumar1890 

To show the cumulative count of open and done tickets correctly in a line chart, you need to make sure each ticket is counted as "Open" from the date it was created until one day before it was resolved, and then counted as "Done" from the resolved date onward. To do this, first create a date table using CALENDER so you can track each day. Then, expand your data so that each ticket appears for every date it was open and done. For example, if a ticket was created on 5/6/2025 and resolved on 5/13/2025, it should show as "Open" from 5/6 to 5/12 and as "Done" from 5/13 onwards. Once you have this expanded data, create two measures using DAX to calculate the cumulative count for "Open" and "Done" status over time. Then use a line chart with Date on the X-axis and the two cumulative measures as lines. This will give you the correct trend where tickets move from open to done as time goes on.

[rakeshkumar1890]

Thanks@rohit1991 for your suggestion,
the same I did - here the issue is that - If I create measure for Done then 134 ticket will show only in Done line graph not in open, beacuse in dax measure, we have to filter staus ="Done".

[rohit1991]

Hi @rakeshkumar1890 

The issue is happening because the DAX measure filters only by status, so it misses the "Open" period for resolved tickets. To solve this, you should create an expanded table in Power Query where each ticket appears with "Open" status from Created to one day before Resolved, and "Done" from Resolved onwards. This way, your DAX can count both statuses correctly without filtering out part of the timeline.

[MohamedFowzan1]

Hi @rakeshkumar1890 

Duplicate the table and have 2, in each of the table expand the lists for Open in One and Done in the other
For OpenDates table,

= Table.AddColumn(PreviousStep, "OpenDates", each
let
start = [Created],
endOpen = if [Resolved Date] <> null then Date.AddDays([Resolved Date], -1) else DateTime.Date(DateTime.LocalNow()),
list = List.Dates(start, Duration.Days(endOpen - start) + 1, #duration(1,0,0,0))
in
list)

For Done Dates table,

= Table.AddColumn(PreviousStep, "DoneDates", each
if [Resolved Date] <> null then
List.Dates([Resolved Date], Duration.Days(DateTime.Date(DateTime.LocalNow()) - [Resolved Date]) + 1, #duration(1,0,0,0))
else
{}
)

Then append both these tables as a new table, remove un necessary columns, for Status just do this:

if [Open Status] = "Open" then "Open" else "Done"

and have one status column

Use the below measures:

Cumulative Open Tickets =
CALCULATE(
DISTINCTCOUNT('Combined'[Id]),
FILTER(
ALL('Combined'),
'Combined'[Date] <= MAX('Combined'[Date]) &&
'Combined'[Status] = "Open"
)
)

Cumulative Done Tickets =
CALCULATE(
DISTINCTCOUNT('Combined'[Id]),
FILTER(
ALL('Combined'),
'Combined'[Date] <= MAX('Combined'[Date]) &&
'Combined'[Status] = "Done"
)
)

Let me know if this works.