Forum Discussion

tmhrzgr's avatar
tmhrzgr
Frequent Visitor
6 years ago
Solved

Get previous row with matching condition

Hi all,

 

I'm currently challenged to realise a target-measurement, moving from Excel with lots of VBA to Power BI. Let me show you two samples in a table "MCBZuLi" first:

 

TicketIDActionDateTimeActionToDoDate
24495528.03.2019 12:50:36Incoming Mail 
24495528.03.2019 13:04:06Ticket created 
24495528.03.2019 13:04:37Note 
24495528.03.2019 13:05:16ToDo01.04.2019
24495501.04.2019 11:30:00Ticket closed 

 

TicketIDActionDateTimeActionToDoDate
24488327.03.2019 12:31:40Incoming Mail 
24483327.03.2019 13:18:09Ticket created 
24488327.03.2019 13:18:23Note 
24483327.03.2019 13:18:26ToDo28.03.2019
24483328.03.2019 11:18:44ToDo29.03.2019
24483329.03.2019 12:04:31Ticket closed 
24483301.04.2019 10:24:24Ticket reopened 
24483301.04.2019 10:26:12ToDo02.04.2019
24483304.04.2019 11:41:01Ticket closed 

 

(Advice: the items in that examples are ordered by TicketID, in the Power BI-list, they aren't.)

 

What do you see here? For every case (Ticket) we have a action-list. In this list we have action-items. Our target is, that a action "ToDo" or "Ticket closed" happens on that day OR earlier, that is set as the new ToDoDate with the last "ToDo"-action. If you set a new ToDo or close the ticket, the target is reached, if not, it's failed.

 

That means: in example 1 the "Ticket closed"-action is happening at 01.04.2019. The ToDoDate from the last "ToDo"-action ist 01.04.2019. So the target is reached. In example 3 following this logic the "ToDo"-action from 28.03.2019 and the "Ticket closed"-action from 29.03.2019 are a "target reached", too, because these actions happens on the previous set ToDoDate. But: the last "Ticket closed" from 04.04.2019 ist a "target failed", beause the previous ToDoDate is the 02.04.2019. So I'm 2 days to late for this action. 🙂

 

So primary with every action that is "ToDo" or "Ticket closed" I have to check: what is the ToDoDate from the previous "ToDo"-action. After that I need to check, if the ActionDateTime is the same day as the previous ToDoDate. If yes, the target for this action is reached, if not, it's failed. Additional I need to check, if there is a previous ToDoDate - in example 2 you see, that the ticket is reopened, after that a ToDo is set. Now the previous ToDoDate isn't coming from the ToDo before the ticket is closed - now it's nothing (like when creating a new ticket with a initial ToDo). This cases should be ignored as there is no ToDoDate for this ToDo-action.

 

I already looked hours for a solution with RANKX, EARLER, FILTER, MAXX and so on, but couldn't find a solution to get my desired values. I hope you could help me to find a solution for that! 🙂

 

Thanks,

Tom

Need Help
  • tmhrzgr's avatar
    tmhrzgr
    6 years ago

    Hi all,

     

    sorry for the delay, but I'm already got the answer by my own. 🙂

     

    FYI: The expected result is a 1 or 0 for every action, that hat the label "ToDo". A 1 is "target reached" and means, on the ToDo-Date of the designated ToDo ist a new ToDo or "Ticket closed"-action. If not, then not.

     

    I made it with a RankX-column for every MCBZuLi-TicketID, that raises every time a new "ToDo"-action is set. Then I can look with a new column for the next RankX if the "ToDo"-value is happening on that designated day. It works as expected. 🙂

     

    For the RankX:

    RankX = RANKX(FILTER(MCBZuLi;BZuLi[TicketID] = EARLIER(MCBZuLi[TicketID]) && OR(MCBZuLi[Action] = "ToDo";MCBZuLi[Action] = "Ticket closed"));MCBZuLi[ActionDateTime];;ASC;DENSE)

     

    For the column that checks if the ToDoDate:

    SlaTarget= 

VAR ThisTicketID = MCBZuLi[TicketID]
VAR ThisRankX    = MCBZuLi[RankX]

VAR NextTodo = IF(MCBZuLi[Aktion] = "ToDo";
                SWITCH(TRUE();
                    NOT(ISBLANK(LOOKUPVALUE(MCBZuLi[Action];MCBZuLi[TicketID];ThisTicketID;MCBZuLi[RankX];ThisRankX + 1;MCBZuLi[Action];"ToDo"))) &&
                    (LOOKUPVALUE(MCBZuLi[ActionDateTime];MCBZuLi[TicketID];ThisTicketID;MCBZuLi[RankX];ThisRankX + 1;MCBZuLi[Action];"ToDo") >= MCBZuLi[ActionDateTime] &&
                    LOOKUPVALUE(MCBZuLi[ActionDateTime];MCBZuLi[TicketID];ThisTicketID;MCBZuLi[RankX];ThisRankX + 1;MCBZuLi[Action];"ToDo") < MCBZuLi[ToDoDate] + 1);
                        1;
                    NOT(ISBLANK(LOOKUPVALUE(MCBZuLi[Aktion];MCBZuLi[TicketID];ThisTicketID;MCBZuLi[RankX];ThisRankX + 1;MCBZuLi[Action];"Ticket closed"))) &&
                    (LOOKUPVALUE(MCBZuLi[ActionDateTime];MCBZuLi[TicketID];ThisTicketID;MCBZuLi[RankX];ThisRankX + 1;MCBZuLi[Action];"Ticket closed") >= MCBZuLi[ActionDateTime] &&
                    LOOKUPVALUE(MCBZuLi[ActionDateTime];MCBZuLi[TicketID];ThisTicketID;MCBZuLi[RankX];ThisRankX + 1;MCBZuLi[Action];"Ticket closed") < MCBZuLi[ToDoDate] + 1);
                        1
                );
                BLANK()
)

RETURN IF(TODAY() > MCBZuLi[ToDoDatum] && MCBZuLi[Action] = "ToDo";IF(NextTodo = 1;1;0);BLANK())

     Not sure if it's the "smartest" way, but hey: it work's and I get what I want. 🙂

2 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi tmhrzgr 

    please provide sample data of the expected result.

    Then I might be able to follow your description.

    Thanks.

     

    • tmhrzgr's avatar
      tmhrzgr
      Frequent Visitor
      6 years ago

      Hi all,

       

      sorry for the delay, but I'm already got the answer by my own. 🙂

       

      FYI: The expected result is a 1 or 0 for every action, that hat the label "ToDo". A 1 is "target reached" and means, on the ToDo-Date of the designated ToDo ist a new ToDo or "Ticket closed"-action. If not, then not.

       

      I made it with a RankX-column for every MCBZuLi-TicketID, that raises every time a new "ToDo"-action is set. Then I can look with a new column for the next RankX if the "ToDo"-value is happening on that designated day. It works as expected. 🙂

       

      For the RankX:

      RankX = RANKX(FILTER(MCBZuLi;BZuLi[TicketID] = EARLIER(MCBZuLi[TicketID]) && OR(MCBZuLi[Action] = "ToDo";MCBZuLi[Action] = "Ticket closed"));MCBZuLi[ActionDateTime];;ASC;DENSE)

       

      For the column that checks if the ToDoDate:

      SlaTarget= 

VAR ThisTicketID = MCBZuLi[TicketID]
VAR ThisRankX    = MCBZuLi[RankX]

VAR NextTodo = IF(MCBZuLi[Aktion] = "ToDo";
                SWITCH(TRUE();
                    NOT(ISBLANK(LOOKUPVALUE(MCBZuLi[Action];MCBZuLi[TicketID];ThisTicketID;MCBZuLi[RankX];ThisRankX + 1;MCBZuLi[Action];"ToDo"))) &&
                    (LOOKUPVALUE(MCBZuLi[ActionDateTime];MCBZuLi[TicketID];ThisTicketID;MCBZuLi[RankX];ThisRankX + 1;MCBZuLi[Action];"ToDo") >= MCBZuLi[ActionDateTime] &&
                    LOOKUPVALUE(MCBZuLi[ActionDateTime];MCBZuLi[TicketID];ThisTicketID;MCBZuLi[RankX];ThisRankX + 1;MCBZuLi[Action];"ToDo") < MCBZuLi[ToDoDate] + 1);
                        1;
                    NOT(ISBLANK(LOOKUPVALUE(MCBZuLi[Aktion];MCBZuLi[TicketID];ThisTicketID;MCBZuLi[RankX];ThisRankX + 1;MCBZuLi[Action];"Ticket closed"))) &&
                    (LOOKUPVALUE(MCBZuLi[ActionDateTime];MCBZuLi[TicketID];ThisTicketID;MCBZuLi[RankX];ThisRankX + 1;MCBZuLi[Action];"Ticket closed") >= MCBZuLi[ActionDateTime] &&
                    LOOKUPVALUE(MCBZuLi[ActionDateTime];MCBZuLi[TicketID];ThisTicketID;MCBZuLi[RankX];ThisRankX + 1;MCBZuLi[Action];"Ticket closed") < MCBZuLi[ToDoDate] + 1);
                        1
                );
                BLANK()
)

RETURN IF(TODAY() > MCBZuLi[ToDoDatum] && MCBZuLi[Action] = "ToDo";IF(NextTodo = 1;1;0);BLANK())

       Not sure if it's the "smartest" way, but hey: it work's and I get what I want. 🙂