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 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])
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.

     

19 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

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

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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
        )
    )

     

    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.