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.

Here you have a link with the report Link

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

Did I answer your question? Mark my post as a solution!

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Frequent Visitor

Re: Backlog per day calculus

After some tweeks, it worked!!!

Thanks a lot for your help