Help with a Calculated Column

[MaironDominguez]

Greetings,

I need help with the code of a calculated column, what I need is that having an initial date, all the tickets of a certain it staff can be organized considering the estimated resolving time.

The idea is that having itstaff Jack, today's date is used for the ticket that is ranked number 1, ranking number 2 will have as its start date the result of adding the start date of ranking 1 and the estimated resolving time, in the same way the start time of ranking number 3 will be calculated. Below I give an example of how what I want would look graphically;

[MaironDominguez]

Understood, im pasting the table that represents my situation for best understanding.

Ranking	ITStaff	Ticket Number	Estimated Resolving Time (Days)	StartDate	EndDate
1	Jack	110	1
1	Nate	111	2
1	Nick	112	4
1	Josh	113	2
2	Jack	114	1
2	Nate	115	3
2	Nick	116	5
2	Josh	117	4
3	Jack	118	2
3	Nate	119	2
3	Nick	120	3
3	Josh	121	6
4	Jack	122	3
4	Nate	123	3
4	Nick	124	3
4	Josh	125	2

ranking is the order of priority that the ticket has for each it staff, there is also the ticket number and the estimated resolving time (days), what I need are two calculated columns.

The start date computed column should have the value of Today() if the ticket rank is 1, but for ticket rank 2, the start date should be today() + estimated resolving time (days) of the rank ticket 2, for the rank 3 ticket the start date must be the rank 2 ticket start date + the rank 2 ticket estimated resolving time and so on.
The end date in each case must be the start date + estimated resolving time.

The ideal results for the example table would be the following;

RankingITStaffTicket NumberEstimated Resolving Time (Days)StartDateEndDate

1	Jack	110	1	08/04/23	08/05/23
1	Nate	111	2	08/04/23	08/06/23
1	Nick	112	4	08/04/23	08/08/23
1	Josh	113	2	08/04/23	08/06/23
2	Jack	114	1	08/05/23	08/06/23
2	Nate	115	3	08/06/23	08/09/23
2	Nick	116	5	08/08/23	08/13/23
2	Josh	117	4	08/06/23	08/10/23
3	Jack	118	2	08/06/23	08/08/23
3	Nate	119	2	08/09/23	08/11/23
3	Nick	120	3	08/13/23	08/16/23
3	Josh	121	6	08/10/23	08/16/23
4	Jack	122	3	08/08/23	08/11/23
4	Nate	123	3	08/11/23	08/14/23
4	Nick	124	3	08/16/23	08/19/23
4	Josh	125	2	08/16/23	08/18/23

The idea is that by choosing a date for the ticket in rank 1 of each itstaff, the dates of the other tickets can be organized automatically.

In the visual that I want to use, it should ideally look as follows.

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

[MaironDominguez]

This in te link to the PBI File;

PBI File

This one is for the excel file im using as a Data Source;

Excel File 

[Anonymous · Accepted solution]

Hi @MaironDominguez ,

Due to today is 8/7/2023, here I use 8/4/2023 as today to have a test to try to get the result you want.

StartDate = 
VAR _TODAY =
    DATE ( 2023, 08, 04 )
RETURN
    IF (
        Table1[Ranking] = 1,
        _TODAY,
        _TODAY
            + CALCULATE (
                SUM ( Table1[Estimated Resolving Time (Days)] ),
                FILTER (
                    ALLEXCEPT ( Table1, Table1[ITStaff] ),
                    Table1[Ranking] < EARLIER ( Table1[Ranking] )
                )
            )
    )

EndDate = Table1[StartDate]+Table1[Estimated Resolving Time (Days)]

Result is as below.

If you want to dynamic today value, you can replace DATE(2023,08,04) by TODAY().

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MaironDominguez]

The following is happening: in some cases, there is a gap between the end date of one ticket and the start date of the next, theoretically that shouldn't happen (indicated with arrows). There's also the case (indicated with a dash) where a ticket hasn't ended when the next one starts. I would like to review this. I would also like to add that if a ticket starts or ends on a Saturday or Sunday, it should start or end on the following Monday.

[MaironDominguez]

Thank you very much, i really appreciate it. I am going to try to adapt this method to the real data and if I have any inconvenience I will let you know in this way.

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.