Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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!!! :)

Need Help