cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Backlog per day calculus

Hello all!!!!

I'm trying to calculate the backlog per day. The idea is to know exactly how many tickets are still open for each day. I created a new table (Dates) and I tried a solution with a cummulative using these formulas:

Dates = calendarauto()
OpenCount = COUNTROWS(filter(Data; Data[CREATION_DATE_UT].[Date] in DATESMTD(Dates[Date])))
CloseCount = COUNTROWS(filter(Data; Data[END_DATE_UT].[Date] in DATESMTD(Dates[Date])))
Backlog = Dates[OpenCount]-Dates[CloseCount]

The problem here is that the calculus resets when we change from month to month.

 Date OpenCount CloseCount Backlog 29/01/2016 0:00 4654 3951 703 30/01/2016 0:00 4656 3951 705 31/01/2016 0:00 4656 3951 705 01/02/2016 0:00 290 263 27 02/02/2016 0:00 565 513 52 03/02/2016 0:00 806 737 69

I tried DATESYTD to but it has the same problem when we change from one year to another, it resets the cummulative count.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Backlog per day calculus

Take a look at these two Quick Measures as I think you want something like them. I have one called Open Tickets.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Proud to be a Datanaut!

2 REPLIES 2
Super User

## Re: Backlog per day calculus

Take a look at these two Quick Measures as I think you want something like them. I have one called Open Tickets.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365