Backlog per day calculus

[Anonymous]

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

Could you please help me? Thanks a lot!!! 🙂

[Greg_Deckler · Accepted solution]

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

[Anonymous]

After some tweeks, it worked!!!

Thanks a lot for your help 🙂