Forum Discussion

MaironDominguez's avatar
MaironDominguez
Frequent Visitor
3 years ago
Solved

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 ti...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
MaironDominguez
Frequent Visitor
3 years ago

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

RankingITStaffTicket NumberEstimated Resolving Time (Days)StartDateEndDate
1Jack1101  
1Nate1112  
1Nick1124  
1Josh1132  
2Jack1141  
2Nate1153  
2Nick1165  
2Josh1174  
3Jack1182  
3Nate1192  
3Nick1203  
3Josh1216  
4Jack1223  
4Nate1233  
4Nick1243  
4Josh1252  

 

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

1Jack110108/04/2308/05/23
1Nate111208/04/2308/06/23
1Nick112408/04/2308/08/23
1Josh113208/04/2308/06/23
2Jack114108/05/2308/06/23
2Nate115308/06/2308/09/23
2Nick116508/08/2308/13/23
2Josh117408/06/2308/10/23
3Jack118208/06/2308/08/23
3Nate119208/09/2308/11/23
3Nick120308/13/2308/16/23
3Josh121608/10/2308/16/23
4Jack122308/08/2308/11/23
4Nate123308/11/2308/14/23
4Nick124308/16/2308/19/23
4Josh125208/16/2308/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's avatar
foodd
Community Champion
3 years ago

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's avatar
    MaironDominguez
    Frequent Visitor
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        MaironDominguez
        Frequent Visitor
        3 years ago

        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.