Count of parts with open status based on date

[Mikeellison]

Hi,

I've read responses to similar request using open and close dates for tickets etc. My query is slightly different in that there is no close date in the table I have. The date value gets updated each time there is a status change. The final status is "closed".

I'd like to count number of parts/tickets that have an open status on any given day and show a running total trend chart over time.

Logic would count number of lines with a date based on date lines being counted (running total) and exclude any items which have a closed status on a given date.

Thanks, Michael 

[Anonymous · Accepted solution]

Hi @Mikeellison 

You can refer to the following sample.

Sample data

1.Create a calendar table and create a relationship between tables.

Calendar = CALENDAR(DATE(2023,1,1),DATE(2024,4,30))

2.Create a measure

Measure = CALCULATE(DISTINCTCOUNT('Table'[ID]),'Table'[Status]="Open")

Then put the following field to visual field

Output

Best Regards!

Yolo Zhu

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