How to calculate last date by transaction groups?

[Shelley]

I have a transaction table of procedure audits. The same procedure may have been audited multiple times, and I want to find the latest date a particular procedure was audited and who conducted the audit. How do I do this?

The fields are all in the same table: Audit end Date, Procedure ID and Audited By ID.

Procedure ID   Audit End Date                 Audited By ID

abc                      8/11/17   1:00 pm             Sam

abc                      9/17/17  7:00am               Fred

abc                      9/17/17  10:00 am             Sally

def                      7/1/17   9:30 am              Sally

def                      8/15/17   11:00 am             Greg

So, I want results to show:

abc, 9/17/17 by Sally

def, 8/15/17 by Greg

and so on.

Help is greatly appreciated. Thanks!

[TomMartens · Accepted solution]

Hey,

I would create a new calculated column in your table that flags the latest date. This new column then can be used as filter or even used on a slicer, this DAX statement for the new calculated column would look like this:

Is latest audit = 
IF('yourtablename'[Audit End Date] = 
CALCULATE(
MAX('yourtablename'[Audit End Date])
,ALLEXCEPT('yourtablename', 'yourtablename'[Procedure ID])
)
,"True"
,"False"
)

Hope this is what you are looking for

Regards

Tom

[Shelley]

Thanks, Tom!

[MarcelBeug]

If the aggregation is part of your data shaping process (i.e. if you only need the latest audits in your data model), then Power Query would be an appropriate way to go:

let
    Source = Audits,
    #"Grouped Rows" = Table.Group(Source, {"Procedure ID"}, {{"Last Audit", each Table.Max(_,each [Audit End Date]), type record}}),
    #"Expanded Last Audit" = Table.ExpandRecordColumn(#"Grouped Rows", "Last Audit", {"Audit End Date", "Audited By ID"}, {"Audit End Date", "Audited By ID"}),
    #"Restored Column Types" = Value.ReplaceType(#"Expanded Last Audit",Value.Type(Source))
in
    #"Restored Column Types"