Forum Discussion

Matt_JEM's avatar
Matt_JEM
Helper I
1 year ago
Solved

Find last transaction date

Good day All.

 

I am new to PowerBi and I apologise if this question have been answered before.

 

How do I create a new colomn with the date where nn is MAX when the date for both transaction dates is the same. It must return a BLANK where nn is MIN when the dates are the same.

 

For the data table below I only expect the date in a new column for where nn= 26 , where ss =19 it must return a blank date.

 

I am trynig to find the absolute last transaction date and time when a stockcode is moved from one warehouse to another. The time stamps from Syspto ERP is hh,mm,ss,nn and PowerBi use hh,mm,ss which result in 2 transactions with exactly the same date and time stamp. Because of this I can not dtermine which is the abolute last transaction. I have filtered out the dates in a column that is the last date and time as per the PowerBi date and time format as below. The NN column is the value of the nn in the Sypro time format hh,mm,ss,nn

Please also see 

https://community.fabric.microsoft.com/t5/Desktop/Tracking-stock-in-different-warehouses/m-p/4274530#M1342838

 

 

StockCodeYearMonthDaynn
F00007-INC001-A01-L00-T032024October2551
F00007-INC001-A01-L00-T07   16
F00007-INC001-A01-L00-T07   21
F00007-INC001-A01-L00-T07   24
F00007-INC001-A01-L00-T07   28
F00007-INC001-A01-L00-T07   66
F00007-INC001-A01-L00-T07   71
F00007-INC001-A01-L00-T07   74
F00007-INC001-A01-L00-T07   77
F00007-INC001-A01-L00-T07   84
F00007-INC001-A01-L00-T07   92
F00007-INC001-A01-L00-T07   98
F00007-INC001-A01-L00-T072024November719
F00007-INC001-A01-L00-T072024November726

 

I thank you in advance for your help.

 

Matt

  • Matt_JEM In PQ, convert the incoming data to absolute milliseconds in a separate column and then you will have what you need.

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    Matt_JEM In PQ, convert the incoming data to absolute milliseconds in a separate column and then you will have what you need.