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!

View solution in original post

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!

View solution in original post

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 1,207 guests
Please welcome our newest community members: