Forum Discussion

MJAGUSIAK's avatar
MJAGUSIAK
Icon for Helper I rankHelper I
2 years ago
Solved

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...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    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.

     

MJAGUSIAK's avatar
MJAGUSIAK
Icon for Helper I rankHelper I
2 years ago

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. 

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
2 years ago

Hi,

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