Help Making Calculated Columns and Gantt Chart

[MaironDominguez]

Good morning,

I would appreciate your assistance with the following: I have the columns Ticket ID, which identifies each ticket; Ranking, which indicates the order in which the tickets will be resolved; IT Staff, who is the person responsible for resolving the ticket; Ticket Status, which represents the current status of the ticket; and Estimated Resolving Days, which denotes the number of days the IT Staff anticipates it will take to resolve the ticket.

With this information, I require help with creating three calculated columns. For these calculations, we will need to utilize a variable named "Today()".

I need a column that accumulatively sums the Estimated Resolving Days column only if the Ticket Status is "Assigned" and this calculation varies for each IT Staff.

The other columns are somewhat simpler:

The Start Date column should be set to "Today()" when the Ticket Ranking is the lowest within that particular IT Staff group. Otherwise, the Start Date should be calculated as "Today()" plus the Estimated Resolving Days of the ticket with the previous Ranking.

Finally, there's the End Date column, which should be the result of adding the Start Date and the Estimated Resolving Days.

I need assistance to create the columns "Acumulated Resolving Days," "Start Date," and "End Date" in order to replicate the process in Power BI, as depicted in the Excel file.

This is the way i want it to look like in Power BI;

I attach the Excel file and the PBI file. If more information is needed, please don't hesitate to request it.

Excel File 

PBI File 

Ticket ID-Ranking-ITStaff-Ticket Status-Estimated Resolving Days-Acumulated Resolving Days-Today-Start Date-End Date

110	0	Bernie	Resolved	1		08/11/23
111	1	Bernie	Assigned	2	2	08/11/23	08/11/23	08/13/23
112	2	Bernie	Assigned	3	5	08/11/23	08/13/23	08/16/23
113	3	Bernie	Assigned	4	9	08/11/23	08/16/23	08/20/23
114	4	Bernie	Resolved	5		08/11/23
115	5	Bernie	Assigned	6	15	08/11/23	08/20/23	08/26/23
116	0	Josh	Assigned	2	2	08/11/23	08/11/23	08/13/23
117	1	Josh	Assigned	4	6	08/11/23	08/13/23	08/17/23
118	2	Josh	Assigned	6	12	08/11/23	08/17/23	08/23/23
119	3	Josh	Assigned	8	20	08/11/23	08/23/23	08/31/23
120	4	Josh	Assigned	10	30	08/11/23	08/31/23	09/10/23
121	5	Josh	Assigned	12	42	08/11/23	09/10/23	09/22/23
122	0	Leo	Resolved	3		08/11/23
123	1	Leo	Resolved	6		08/11/23
124	2	Leo	Resolved	9		08/11/23
125	3	Leo	Assigned	12	12	08/11/23	08/11/23	08/23/23
126	4	Leo	Assigned	15	27	08/11/23	08/23/23	09/07/23
127	5	Leo	Assigned	18	45	08/11/23	09/07/23	09/25/23
128	0	Max	Assigned	1.5	1.5	08/11/23	08/11/23	08/12/23
129	1	Max	Assigned	3	4.5	08/11/23	08/12/23	08/15/23
130	2	Max	Assigned	4.5	9	08/11/23	08/15/23	08/20/23
131	3	Max	Assigned	6	15	08/11/23	08/20/23	08/26/23
132	4	Max	Assigned	7.5	22.5	08/11/23	08/26/23	09/02/23
133	5	Max	Assigned	9	31.5	08/11/23	09/02/23	09/11/23
134	0	Peter	Resolved	2.5		08/11/23
135	1	Peter	Resolved	5		08/11/23
136	2	Peter	Resolved	7.5		08/11/23
137	3	Peter	Resolved	10		08/11/23
138	4	Peter	Assigned	12.5	12.5	08/11/23	08/11/23	08/23/23
139	5	Peter	Assigned	15	27.5	08/11/23	08/23/23	09/07/23

[Anonymous · Accepted solution]

Hi @MaironDominguez,

You can try to use following calculated column formulas if they suitable for your requirement:

AR Days = 
IF (
    Table1[Ticket Status] <> "Resolved",
    CALCULATE (
        SUM ( Table1[Estimated Resolving Days] ),
        FILTER (
            Table1,
            [ITStaff] = EARLIER ( Table1[ITStaff] )
                && [Ranking] <= EARLIER ( Table1[Ranking] )
                && [Ticket Status] <> "Resolved"
        )
    )
)

SDate = 
VAR cum =
    CALCULATE (
        SUM ( Table1[Estimated Resolving Days] ),
        FILTER (
            Table1,
            [ITStaff] = EARLIER ( Table1[ITStaff] )
                && [Ranking] < EARLIER ( Table1[Ranking] )
                && [Ticket Status] <> "Resolved"
        )
    )
RETURN
    IF ( Table1[Ticket Status] <> "Resolved", [Today] + cum )

EDate = 
IF (
    Table1[Ticket Status] <> "Resolved",
    [SDate] + [Estimated Resolving Days]
)

Notice: if you want these date dynamic changes based system date, you can replace the [Today] column to TODAY() function.

Regards,
Xiaoxin Sheng

[MaironDominguez]

Wonderful, it works perfectly, thank you very much!