Forum Discussion

MJAGUSIAK's avatar
MJAGUSIAK
Helper 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.

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

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]

MJAGUSIAK's avatar
MJAGUSIAK
Helper I
2 years ago

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.