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


I have book! Learn Power BI from Packt


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


I have book! Learn Power BI from Packt


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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 111 members 1,540 guests
Please welcome our newest community members: