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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Shelley
Continued Contributor
Continued Contributor

How to calculate last date by transaction groups?

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!

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

3 REPLIES 3
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Shelley
Continued Contributor
Continued Contributor

Thanks, Tom!

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"
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

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.