Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MJAGUSIAK
Helper I
Helper I

DAX help: most recent event

I am trying to find the most recent event in my PBI. 
Here are my column names and I need to find the last status update under "Last Event Description". I created a new column that combines the last event day and time. 

Customer Confirmation NumberDelivery Confirmation NumberManifest DateReceive DateLast Event DateLast Event TimeLast Event DescriptionZip or Postal Code

 

Please let me know how I should go about getting this most recent status. 

I tried to use the following but its not pulling back the latest status:

Last Status =
VAR CustomerNumber = 'DHL Status'[Delivery Confirmation Number]

RETURN
maxx(FILTER(ALL('DHL Status'), 'DHL Status'[Delivery Confirmation Number] = CustomerNumber),
    'DHL Status'[Last Event Description])
1 ACCEPTED SOLUTION

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Customer Confirmation Number", "Delivery Confirmation Number"}, {{"Count", each Table.Max(_,{"Last Event Date","Last Event Time"})}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Manifest Date", "Receive Date", "Last Event Date", "Last Event Time", "Last Event Description"}, {"Manifest Date", "Receive Date", "Last Event Date", "Last Event Time", "Last Event Description"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Count",{{"Last Event Date", type date}, {"Last Event Time", type time}})
in
    #"Changed Type"

Hope this helps.

Ashish_Mathur_0-1706751963810.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

19 REPLIES 19
v-kongfanf-msft
Community Support
Community Support

Hi @MJAGUSIAK ,

 

You can try formula like below:

Last Status = 
VAR CustomerNumber =
    MAX ( 'DHL Status'[Delivery Confirmation Number] )
RETURN
    CALCULATE (
        MAXX (
            FILTER (
                ALL ( 'DHL Status' ),
                'DHL Status'[Delivery Confirmation Number] = CustomerNumber
            ),
            'DHL Status'[Last Event Date] + 'DHL Status'[Last Event Time]
        ),
        FILTER (
            'DHL Status',
            'DHL Status'[Delivery Confirmation Number] = CustomerNumber
        )
    )

vkongfanfmsft_0-1706669682061.png

 

Best Regards,
Adamk Kong

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

This formula works for giving me the last date and time that is in the data. 
I need to use the Delivery Confirmation Number and look for the most recent date and then take that most recent date and display whatever is in the column for Last Event Description. 
Basically, I am wanting to override any previously seen status codes and only use the most recent status in the data for that particular customer tracking number. 

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result as well.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Customer Confirmation NumberDelivery Confirmation NumberManifest DateReceive DateLast Event DateLast Event TimeLast Event Description
'78510745'92748903261399047233141/25/2024 5:071/22/2024 19:021/26/20246:10:00 AMOUT FOR DELIVERY
'78542345'92748903261399047394521/25/2024 5:071/23/2024 18:581/26/20246:21:00 AMOUT FOR DELIVERY
'78510884'92748903261399047361611/25/2024 5:071/23/2024 18:581/26/20246:55:00 AMOUT FOR DELIVERY
'78541001'92612903261399047379401/25/2024 1:081/23/2024 18:591/26/20246:10:00 AMOUT FOR DELIVERY
'78510745'92748903261399047233141/25/2024 5:071/22/2024 19:021/26/20246:10:00 AMDELIVERED
'78542345'92748903261399047394521/25/2024 5:071/23/2024 18:581/26/20246:21:00 AMDELIVERED
'78510884'92748903261399047361611/25/2024 5:071/23/2024 18:581/26/20246:55:00 AMDELIVERED
'78541001'92612903261399047379401/25/2024 1:081/23/2024 18:591/26/20246:10:00 AMDELIVERED
       

Hi,

Based on the Table that you have shared, please also show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Sorry, didnt catch that when I created the sample data. please use below. 

Customer Confirmation NumberDelivery Confirmation NumberManifest DateReceive DateLast Event DateLast Event TimeLast Event Description
'78510745'92748903261399047233141/25/2024 5:071/22/2024 19:021/25/20246:10:00 AMOUT FOR DELIVERY
'78542345'92748903261399047394521/25/2024 5:071/23/2024 18:581/25/20246:21:00 AMOUT FOR DELIVERY
'78510884'92748903261399047361611/25/2024 5:071/23/2024 18:581/25/20246:55:00 AMOUT FOR DELIVERY
'78541001'92612903261399047379401/25/2024 1:081/23/2024 18:591/25/20246:10:00 AMOUT FOR DELIVERY
'78510745'92748903261399047233141/25/2024 5:071/22/2024 19:021/26/20246:10:00 AMDELIVERED
'78542345'92748903261399047394521/25/2024 5:071/23/2024 18:581/26/20246:21:00 AMDELIVERED
'78510884'92748903261399047361611/25/2024 5:071/23/2024 18:581/26/20246:55:00 AMDELIVERED
'78541001'92612903261399047379401/25/2024 1:081/23/2024 18:591/26/20246:10:00 AMDELIVERED
       

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Customer Confirmation Number", "Delivery Confirmation Number"}, {{"Count", each Table.Max(_,{"Last Event Date","Last Event Time"})}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Manifest Date", "Receive Date", "Last Event Date", "Last Event Time", "Last Event Description"}, {"Manifest Date", "Receive Date", "Last Event Date", "Last Event Time", "Last Event Description"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Count",{{"Last Event Date", type date}, {"Last Event Time", type time}})
in
    #"Changed Type"

Hope this helps.

Ashish_Mathur_0-1706751963810.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish, 

My dev team changed the file for me to use. I need to use the tracking number and the activity date to see the most up to date activity code. I need to see how the M code would be for these columns because I keep getting errors when trying to use the code from above and just insert it into the m code with changing the needed names:

 

 

Warehouse CodeClient NameCarrier CodeShip DateTracking NumberError Desc from CarrierProcess flagActivity CodeActivity Date
73011KS DFW900000112024/02/021ZR637F60107746480 FD20240205

 

Here is my M code that works right now but is not sorted:
let
Source = SharePoint.Files("https://pfsweb0.sharepoint.com/sites/CarrierTracking/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://pfsweb0.sharepoint.com/sites/CarrierTracking/Web Focus reports/")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (3)", each #"Transform File (3)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (3)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (3)", Table.ColumnNames(#"Transform File (3)"(#"Sample File (3)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Warehouse Code", Int64.Type}, {"Client Name", type text}, {"Carrier Code", Int64.Type}, {"Ship Date", type date}, {"Tracking Number", type text}, {"Error Desc from Carrier", type any}, {"Process flag", type text}, {"Activity Code", type text}, {"Activity Date", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"})
in
#"Removed Columns"

 

Cannot understand what you want.  Share some data to work with, explain the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

This worked great. much appreciated. 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

It should pull back the data where it is showing only a unique value for Delivery Confirmation Number with the most recent "Last date and time" showing the "last Event Description"

Customer Confirmation NumberDelivery Confirmation NumberManifest DateReceive DateLast Event DateLast Event TimeLast Event Description
'78510745'92748903261399047233141/25/2024 5:071/22/2024 19:021/26/20246:10:00 AMDELIVERED
'78542345'92748903261399047394521/25/2024 5:071/23/2024 18:581/26/20246:21:00 AMDELIVERED
'78510884'92748903261399047361611/25/2024 5:071/23/2024 18:581/26/20246:55:00 AMDELIVERED
'78541001'92612903261399047379401/25/2024 1:081/23/2024 18:591/26/20246:10:00 AMDELIVERED

9274890326139904723314 has 2 entries and both have the same end date and time.  Why shoud the delivered row show up in the result. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

The expected result is the following and it should not show back the other rows:

'78541001'92612903261399047379401/25/2024 1:081/23/2024 18:591/26/20246:10:00 AMDELIVERED
'78541001'92612903261399047379401/25/2024 1:081/23/2024 18:591/26/20246:10:00 AMDELIVERED

Hi,

"Last Event Description" is a column with text entries.  So what do you mean by "Find the last status update"? [from your first post]


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

The data will have entries with duplicates of Delivery Confirmation Numbers whenever the status of the Last Event Description changes. I need to use the Delivery Confirmation Number and look for the most recent date and then take that most recent date and display whatever is in the column for Last Event Description. 
Basically, I am wanting to override any previously seen status codes and only use the most recent status in the data for that particular customer tracking number. 

 

So if there are duplicate numbers then i need to use the most recent entry to display and use for calculations. 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Customer Confirmation NumberDelivery Confirmation NumberManifest DateReceive DateLast Event DateLast Event TimeLast Event Description
'78510745'92748903261399047233141/25/2024 5:071/22/2024 19:021/26/20246:10:00 AMOUT FOR DELIVERY
'78542345'92748903261399047394521/25/2024 5:071/23/2024 18:581/26/20246:21:00 AMOUT FOR DELIVERY
'78510884'92748903261399047361611/25/2024 5:071/23/2024 18:581/26/20246:55:00 AMOUT FOR DELIVERY
'78541001'92612903261399047379401/25/2024 1:081/23/2024 18:591/26/20246:10:00 AMOUT FOR DELIVERY
'78510745'92748903261399047233141/25/2024 5:071/22/2024 19:021/26/20246:10:00 AMDELIVERED
'78542345'92748903261399047394521/25/2024 5:071/23/2024 18:581/26/20246:21:00 AMDELIVERED
'78510884'92748903261399047361611/25/2024 5:071/23/2024 18:581/26/20246:55:00 AMDELIVERED
'78541001'92612903261399047379401/25/2024 1:081/23/2024 18:591/26/20246:10:00 AMDELIVERED
       
lbendlin
Super User
Super User

Your VAR CustomerNumber returns a table.  You may be missing the aggregation function.

 

A simple way would be to use TOPN(1) and CONCATENATEX.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.