Get previous row with matching condition

[tmhrzgr]

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:

TicketID	ActionDateTime	Action	ToDoDate
244955	28.03.2019 12:50:36	Incoming Mail
244955	28.03.2019 13:04:06	Ticket created
244955	28.03.2019 13:04:37	Note
244955	28.03.2019 13:05:16	ToDo	01.04.2019
244955	01.04.2019 11:30:00	Ticket closed

TicketID	ActionDateTime	Action	ToDoDate
244883	27.03.2019 12:31:40	Incoming Mail
244833	27.03.2019 13:18:09	Ticket created
244883	27.03.2019 13:18:23	Note
244833	27.03.2019 13:18:26	ToDo	28.03.2019
244833	28.03.2019 11:18:44	ToDo	29.03.2019
244833	29.03.2019 12:04:31	Ticket closed
244833	01.04.2019 10:24:24	Ticket reopened
244833	01.04.2019 10:26:12	ToDo	02.04.2019
244833	04.04.2019 11:41:01	Ticket 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

[ImkeF]

Hi @tmhrzgr 

please provide sample data of the expected result.

Then I might be able to follow your description.

Thanks.

[tmhrzgr · Accepted solution]

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. 🙂