Help to get next timestamp from first timestamp

[Anonymous]

Hi guys,

I am trying to get the pairs of "00 Initialization: Assigning all transaction item variables" and "Transaction number X was successfully processed.", and their time stamps. There might be a record of "00 Initialization: Assigning all transaction item variables" in between, that should be ignored. In the same day and for the same JobKey, I need to find the timestamp from the first instance of A to the first instance of B. In a day, there might be more than 1 pair, as indicated by the highlighted-blue (I need those time stamp too).

A: 00 Initialization: Assigning all transaction item variables

B: Transaction number X was successfully processed.

link to file: https://drive.google.com/file/d/1guGmTxp8Z2lhxMWTrxmf7pnhrRDQxTyR/view?usp=sharing

Table 1

00 Initialization: Assigning all transaction item variables	2/26/21 2:55 AM
00 Initialization: Transaction type: Permits. Performing Local Council Permit Renewals	2/26/21 2:55 AM
10 Initialize Matrix Tables: Initializing config variables to be used for processing	2/26/21 2:55 AM
20 Split Submissions Entry: Navigating to Invoices to Split	2/26/21 2:55 AM
20 Split Submissions Entry: Finding submission entry to split	2/26/21 2:55 AM
20 Split Submissions Entry: Submission to split found	2/26/21 2:55 AM
........
00 Initialization: Transaction type: Permits. Performing Local Council Permit Renewals	2/26/21 2:58 AM
.......
Transaction number 2 was successfully processed.	2/26/21 3:03 AM
.......
00 Initialization: Assigning all transaction item variables	2/26/21 3:03 AM
...........
Transaction number 3 was successfully processed.	2/26/21 3:06 AM

I have tried with the following DAX, but NP AHT GetFirstAssignAllTransactionItemVariables only returns the first timestamp of A (belonging to the red pair). I have inserted the link to the excel with all the column headers. Assistance is much appreciated.

NP AHT GetFirstAssignAllTransactionItemVariables =

MINX(

FILTER(

Logs,

Logs[ProcessName] = "RPANET009.Esker.Payments.Management.Performer_RPANET009 Application, Payments Management of Local Council Permits"

&& Logs[JobKey] = EARLIER(Logs[JobKey])

&& Logs[TimeStamp] < EARLIER(Logs[TimeStamp])

&& SEARCH("00 Initialization: Assigning all transaction item variables", [Message],,BLANK())>0

),

Logs[TimeStamp]

)

NP AHT GetNextSuccessfulProcessed =

MINX(

FILTER(

Logs,

Logs[ProcessName] = "RPANET009.Esker.Payments.Management.Performer_RPANET009 Application, Payments Management of Local Council Permits"

&& Logs[JobKey] = EARLIER(Logs[JobKey])

&& Logs[TimeStamp] >= EARLIER(Logs[TimeStamp])

&& SEARCH("was successfully processed.", [Message],,BLANK())>0

),

Logs[TimeStamp]

)

[AlB · Accepted solution]

@Anonymous 

It is, but I'd certainly recommend to do it in PQ. Way simpler. Create a new calculated table. See attached file

NewTable = 
VAR baseT_ =
    FILTER (
        SUMMARIZE (
            Table1,
            Table1[JobKey],
            Table1[DayStamp],
            Table1[Message],
            Table1[TimeStamp]
        ),
        Table1[Message] = "00 Initialization: Assigning all transaction item variables"
            || (
                FIND ( "Transaction number", [Message], 1, 0 ) > 0
                    && FIND ( "was successfully processed", [Message], 1, 0 )
            )
    )
RETURN
    FILTER (
        baseT_,
        VAR previousTrans_ =
            MAXX (
                FILTER (
                    baseT_,
                    [TimeStamp] < EARLIER ( [TimeStamp] )
                        && FIND ( "Transaction number", [Message], 1, 0 ) > 0
                        && FIND ( "was successfully processed", [Message], 1, 0 ) > 0
                        && [DayStamp] = EARLIER ( [DayStamp] )
                ),
                [TimeStamp]
            )
        VAR previousIni_ =
            MAXX (
                FILTER (
                    baseT_,
                    [TimeStamp] < EARLIER ( [TimeStamp] )
                        && [Message] = "00 Initialization: Assigning all transaction item variables"
                        && [DayStamp] = EARLIER ( [DayStamp] )
                ),
                [TimeStamp]
            )
        RETURN
            IF (
                [Message] = "00 Initialization: Assigning all transaction item variables",
                previousIni_ <= previousTrans_,
                TRUE ()
            )
    )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

Thanks @AlB! Just applied the solution to my company's real-data and it worked like a charm! 5 stars to you my friend!

[AlB]

Hi @Anonymous 

You can do this in the query editor. This would be the M code that reads the data from your Excel file and extracts the time stamps you need. See it all at work in the attached file.

let
    Source = Excel.Workbook(File.Contents("d:\_system\Desktop\Forum\dashboard_network_permits_getAHT2.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"ID", Int64.Type}, {"SD", type datetime}, {"ED", type datetime}, {"JobKey", type text}, {"Source", Int64.Type}, {"Message", type text}, {"TimeStamp", type datetime}, {"ProcessName", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Message] = "00 Initialization: Assigning all transaction item variables" or (Text.Contains([Message], "Transaction number") and Text.Contains([Message], "was successfully processed")))),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "DayStamp", each Date.From([TimeStamp]), type date),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"TimeStamp", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"JobKey", "DayStamp", "Message"}, {{"TimeStamp", each List.Min([TimeStamp]), type datetime }}, GroupKind.Local)
in
    #"Grouped Rows"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

hi @AlB ,

Tx. for your quick response! Is is possible to get the solution in DAX?

figure 1 - correct pairing of A and B using DAX NP AHT GetFirstAssignAllTransactionItemVariables and NP AHT GetNextSuccessfulProcessed, from 2/26/2021 2:55 AM to 2/26/2021 3:03 AM.

figure 2 - Incorrect pairing of A and B using DAX NP AHT GetFirstAssignAllTransactionItemVariables and NP AHT GetNextSuccessfulProcessed, from 2/26/2021 2:55 AM (supposed to be 2/26/21 3:03 AM ) to 2/26/2021 3:06 AM.