Extract latest transaction: Variable attributes.

[ToddMate]

Hi 

I have a table that contains transactions for a service (service_id) as shown below:

service_id	id	date_created	date_modified	direction	id	new_plan_id	old_plan_id	result	username	wsm
135052	98579	28/05/2019	28/05/2019	UPGRADE	6278	6004	6000	SUCCESS	899266	16AFBEB5DCD16
135052	98579	30/08/2019	30/08/2019	CHURN	31525	52653	52653	COMPLETED	899266	5265342
135035	295020	30/05/2019	30/05/2019	UPGRADE	19283	4631	4655	SUCCESS	547754	5E64E1044
135035	295020	28/09/2019	28/09/2019	DOWNGRADE	32886	53939	53939	COMPLETED	547754	5393912

What i would like to do is build a visual that shows me just the latest transaction (using the date_created) per service_id.

So the outcome would be  just seeing these transactions:

service_id	id	date_created	date_modified	direction	id	new_plan_id	old_plan_id	result	username	wsm
135052	98579	30/08/2019	30/08/2019	CHURN	31525	52653	52653	COMPLETED	899266	5265342
135035	295020	28/09/2019	28/09/2019	DOWNGRADE	32886	53939	53939	COMPLETED	547754	5393912

Cam anyone assist with this.

Thank you in advance

Todd

[Ashish_Mathur]

Hi,

I notice that the result column on the last day is completed for both service_id's.  So just apply a filter on completed in the visual.

[ToddMate]

In this example the status was just coincidental. 

[v-diye-msft · Accepted solution]

Hi @ToddMate 

Use below measure:

Measure = var a = CALCULATE(MAX('Table'[date_created]),ALL('Table'),VALUES('Table'[service_id]))
return
IF(a=MAX('Table'[date_created]),a,BLANK())

Then filter the measure is not blank :

[Anonymous]

Hi @ToddMate  - 

First, you can make a new column, which identifies whether the row is the latest for the service_id. There are 2 ways to do this, PowerQuery (M) or DAX. Note: I changed the date format to U.S. so that it would work for me.

M Script Steps to identify latest:

1. Use Group By to get the max date for each service_id.
2. Merge (Left Join) the grouped table into the original table on service_id and date.
3. Add new column which is False if the merged values are NULL, otherwise true.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDLDsIgEEX/pWuTzoOhzFIBdeEr1sZF4///hlBjO0ZdnbmZwOEyjg2ygFCzajRIp4UgLYWWAL/CcNld1ymXyVMXKgDcC1DQDzHmvi9TUCXvy4B+vd3kjaSY0DeP1S9daBlmnQ1xP1xPhYxCUijkhQ3j+Xg55FtO1jgtHc0uridJBQhedYzMhqUbKoV6vfOME0Q+yonrOqm1JXuXEZz7J1P7kTak8/301jGFMD2cldXQtluUdYml3eMJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [service_id = _t, id = _t, date_created = _t, date_modified = _t, direction = _t, id.1 = _t, new_plan_id = _t, old_plan_id = _t, result = _t, username = _t, wsm = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"service_id", Int64.Type}, {"id", Int64.Type}, {"date_created", type date}, {"date_modified", type date}, {"direction", type text}, {"id.1", Int64.Type}, {"new_plan_id", Int64.Type}, {"old_plan_id", Int64.Type}, {"result", type text}, {"username", Int64.Type}, {"wsm", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"service_id"}, {{"Max Date_Created", each List.Max([date_created]), type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"service_id", "date_created"}, #"Grouped Rows", {"service_id", "Max Date_Created"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"service_id"}, {"Grouped Rows.service_id"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "Is Latest - M", each [Grouped Rows.service_id] <> null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Grouped Rows.service_id"})
in
    #"Removed Columns"

 DAX Script to identify latest:

1. Store the service_id of the current row in a variable.
2. Store the max date for the service_id in a variable.
3. Return True if the date equals the max date, otherwise false.

Is Latest - DAX = 
var _current_sid = [service_id]
var _max_create_date = 
    CALCULATE(
        MAX([date_created]),
        ALL(Table2),
        Table2[service_id] = _current_sid
    )
return [date_created] = _max_create_date

Then, you can use your new column to filter. The attached PBIX does this with each new column on separate pages.

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.