Forum Discussion
Help with a Calculated Column
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;
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
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-Forum/ba-p/963216
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.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.
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).
This in te link to the PBI File;
This one is for the excel file im using as a Data Source;
