cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mickkk666 Frequent Visitor
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.

 

DateOpenCountCloseCountBacklog
29/01/2016 0:0046543951703
30/01/2016 0:0046563951705
31/01/2016 0:0046563951705
01/02/2016 0:0029026327
02/02/2016 0:0056551352
03/02/2016 0:0080673769

 

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

 

Could you please help me? Thanks a lot!!! Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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
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!


Highlighted
mickkk666 Frequent Visitor
Frequent Visitor

Re: Backlog per day calculus

After some tweeks, it worked!!!


Thanks a lot for your help Smiley Happy