Backlog problem.

[Anonymous]

Hi everyone, 

I'm currently having a problem. 

So my idea is to create a backlog of open and closed tickets per month and the ones who transitioned to the next month because they were not closed. Thanks to the help of another person, I got close to the answer the problem is that right now the backlog doesn't split by Type. 

This is the table that I have: 

As you can see some tickets are created and not closed in the same month. So these tickets would be considered backlog from previous months. 

Since this table is just a mockup for the actual project, Although here might not make sense but these are the relations that I must have in the project. 

Currently I'm using this measure: 

Cumulative Total =

var maxdate=Date(Year(max('Sheet1'[created ])), Month(max('Sheet1'[created ])), 01)

var resolved=Date(Year(max('Sheet1'[closed ])), Month(max('Sheet1'[closed ])), 01)

RETURN

CALCULATE(

    Count('Sheet1'[tickets ]),

    FILTER(

        All('Sheet1'),

       ('Sheet1'[created ]<maxdate && 'Sheet1'[closed ]=blank()) || ( 'Sheet1'[created ]<maxdate && EOMONTH('Sheet1'[closed ],0)>=EOMONTH(max('Sheet1'[created ]),0))))

This is my current result :

Eventhough this works, it doesn't work to the fullest concept that I have in mind since I can't collect tickets by their type. The backlog total is correct, but for example, in april, I would like for it to show 2 tickets in the backlog for Type1 because there are two tickets who are yet to be closed in this month. 

This would be the ideal result: 

As you can see the tickets are also registred in the backlog from each past month. August also shows up because it is the month and already has the ticket created in july. 

Any help will be is appreciated. 

Thank you. 

If needed, I can send this mock up to anyone who wants to test it. 

@amitchandak @Ahmedx @Sahir_Maharaj 

[Ahmedx]

[Ahmedx]

Is this what you are looking for?

[amitchandak · Accepted solution]

@Anonymous , Seem like very similar to HR active employee

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

If needed file attached after signature

[Anonymous]

Hi! Thanks for the help! The video shows a partial solution, but it doesn't appear registred in the next month as I needed it. So january should come with zero backlog, and february with the backlog from the tickets that were not closed in the previous month. Any idea how to do this? Thank you very much once again!