Daily Cumulative backlog based on status

[MK_BI]

Hi Team,

I am not an power BI expert, i am handling support and i want to create a daily backlog report for my support tickets (my source is JIRA), can someone help me for this.
I have 2 tables, one Issue table and second Issue History table where i can see the status changes complete history, so far what i done is following 

• 1. First I have created a last know status column in the history table using the following formula.
  Last_Status = CALCULATE(MAXX(TOPN(1,Histories,Histories[CREATED],DESC),Histories[NEW_VALUE_FORMATTED]),ALLEXCEPT(Histories,Histories[Crd_Date],Histories[ISSUE_KEY]))
  Note: Here CRD_Date is a converted date column to remove timestamp 

Crd_Date = FORMAT(Histories[CREATED], "DD/MMM/YYYY")

• 2. Then using Issue History table i have created a calculated table called 'Last_Status' with the following formula.

Last_Status = SUMMARIZE(

    Histories,

    Histories[ISSUE_KEY],

    Histories[Crd_Date],

    "Last_Status",

    CALCULATE(

        MAX(Histories[Last_Status]),

        FILTER(Histories,Histories[Crd_Date]=MAX(Histories[Crd_Date])) ))

• 3. Then last i created a new date table using the min and max dates of Issues table and table started creating the following columns 
  • Date
  • Daily created count
  • Daily resolved count
  • Daily Customer bucket count
    
    CustomerDaily_Bucket = CALCULATE(COUNTROWS(Last_Status),FILTER(Last_Status,Last_Status[Crd_Date]=Calender[Date] && Last_Status[Last_Status]="A")) +

    CALCULATE(COUNTROWS(Last_Status),FILTER(Last_Status,Last_Status[Crd_Date]=Calender[Date] && Last_Status[Last_Status]="B"))+

    CALCULATE(COUNTROWS(Last_Status),FILTER(Last_Status,Last_Status[Crd_Date]=Calender[Date] && Last_Status[Last_Status]="C")) + 0

• Daily My bucket count 
  

My_Daily_Bucket = CALCULATE(COUNTROWS(Last_Status),FILTER(Last_Status,Last_Status[Crd_Date]=Calender[Date] && Last_Status[Last_Status]="E")) +

CALCULATE(COUNTROWS(Last_Status),FILTER(Last_Status,Last_Status[Crd_Date]=Calender[Date] && Last_Status[Last_Status]="F")) + 0

• Cummulative Daily count
  
  Cummulative = CALCULATE(SUM('Calender'[Crt_CALC]),FILTER(ALL(Calender[Date]),Calender[Date]<=MAX(Calender[Date])))

Now my problem is how to identify daily end of the day backlog for customer and me, pls understand since its JIRA as per workflow the ticket can be moved from any status to any until finally its resolved.

My problem is with the below red highlighted ones, i cannot simply use cumulative formula like daily cumulative for bucket list and i cant do status by status mapping as well since i have closer to 50 status overall (several ticket types included). Now how can i achieve everyday business closing hours how many tickets where lying in my queue and customer queue.

 
your assistance is much appreciated. 

[MK_BI]

@amitchandak 
Thanks for your assitance, the formula what you briefed is bit complex for me but still one thing i would like to highlight with my source is, if i compare my ticket history table to your employee table the major difference would be there is no unique value in my table, the Ticket ID and Ticket No will repeat several time.

If its a just a straight forward Created to Resolved this would work but what i talking is as follows

In the above mock table, for a single ticket over a period of 9 day several transaction happened, if its a single ticket then my date column is the unique key here, but my history table is for everything so there is no unique key at all in my table. The red highlighted ones on 12th and 14th are just mock entries i post to explain there wont be any transaction at all, and finally on 18th is when ticket got closed from that day onward there wont be any transactions.

What i want is to present a line chart indicating backlog bucket for me and customer day wise. In this scenario i only two options here 
1. Somehow try to calculate the transistion duration as a measure so that when i include that in chart it will project correct numbers.

2. Create a data table using my history table with the following logic

• Basic a ticket created and resolved date i should create a rows with Created date as starting and resolved date as ending for every day for every ticket.
• Example as per above mock table, i should created 9 records on my mock table instead of 7 records which is in my source table with 12th and 14th dated rows inserted by me using formula and retrive the last know status in that (12th status would be E and 14th status would be B).
• Then link this new table to my Date table, with one to many relation
• Creat a measure using this relation and identify daily backlog for me and customer (no need for cummulative calculation here).

Now my problem is i know the direction i am heading and the mode i can take but i dont have the tool with me, i dont know the formula for either of this approach. Pls also highlight if my understanding is correct on these 2 approach or if there is a different approach i can try beyond these 2.

[amitchandak]

@MK_BI , There should be 2 dates created and resolved. If yes, then you can follow HR approach of 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-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

[MK_BI]

Sorry instead of reply to your comment i reply to the main post can you check that pls.