Get Current status based on the latest date and group by id

[Anonymous]

Hi Team,

I need to create a custom column that contains the current status for an id based on the latest date and group by id. please find the below table

Stage	status	id	ForWeek	Currrent Status
Execute	Amber	1	2/20/2017	Amber
Execute	green	1	1/19/2017	Amber
Define	red	2	2/23/2017	Red
Execute	amber	2	02/09/2017	Red
Execute	green	2	02/08/2017	Red
Define	amber	3	2/17/2017	Amber

so the end result should be like this

Stage	status	id	ForWeek	Currrent Status
Execute	Amber	1	2/20/2017	Amber
Define	red	2	2/23/2017	Red
Define	amber	3	2/17/2017	Amber

please help me with this functionality.

Regards,

Sivaaprataap

[MarcelBeug · Accepted solution]

With your data copied to Excel I created the code below in Power Query Excel.

It can be usd in Power BI Query Editor with adjusted Source.

The "SelectLatest" step is a function that is used in the next step to filter the nested tables (in AllData, from the "Grouped Rows"step). The code in "SelectLatest" is based on the code that is generated when you filter a date column on "Is Earliest" (drop-down in column header - Date Filters - Is Earliest)

Otherwise the steps are all standard UI steps.

For"#"Changed Type1" I selected all columns and used "Detect Data Type" on the "Transform" tab,  and then adjusted the type for "ForWeek" to date.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Stage", type text}, {"status ", type text}, {"id", Int64.Type}, {"ForWeek", type datetime}, {"Currrent Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"AllData", each _, type table}}),
    SelectLatest = (Group as table) as table => Table.SelectRows(Group, let latest = List.Max(Group[ForWeek]) in each [ForWeek] = latest),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "LatestData", each SelectLatest([AllData])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllData"}),
    #"Expanded LatestData" = Table.ExpandTableColumn(#"Removed Columns", "LatestData", {"Stage", "status ", "ForWeek", "Currrent Status"}, {"Stage", "status ", "ForWeek", "Currrent Status"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded LatestData",{"Stage", "status ", "id", "ForWeek", "Currrent Status"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Stage", type text}, {"status ", type text}, {"id", Int64.Type}, {"ForWeek", type date}, {"Currrent Status", type text}})
in
    #"Changed Type1"

[Phil_Seamark · Accepted solution]

Here is a DAX based solution.  I've assumed your table is called 'Status' and the code below can be used to create a new table from the modelling tab

New Table = 
VAR MaxDates = SELECTCOLUMNS(
                         SUMMARIZE('Status','Status'[id] , "Max Date" , MAX('Status'[ForWeek])),
                         "ID2",[id],
                         "Max Date",[Max Date])
                         
VAR r = FILTER( CROSSJOIN('Status',MaxDates),[ID2]=[id] && [Max Date] = [ForWeek])
RETURN SELECTCOLUMNS(
					r,
					"Stage",[Stage],
					"Status",[status],
					"id",[id],
					"ForWeek",[ForWeek],
					"Current Status",[Currrent Status]
					)

[MarcelBeug · Accepted solution]

With your data copied to Excel I created the code below in Power Query Excel.

It can be usd in Power BI Query Editor with adjusted Source.

The "SelectLatest" step is a function that is used in the next step to filter the nested tables (in AllData, from the "Grouped Rows"step). The code in "SelectLatest" is based on the code that is generated when you filter a date column on "Is Earliest" (drop-down in column header - Date Filters - Is Earliest)

Otherwise the steps are all standard UI steps.

For"#"Changed Type1" I selected all columns and used "Detect Data Type" on the "Transform" tab,  and then adjusted the type for "ForWeek" to date.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Stage", type text}, {"status ", type text}, {"id", Int64.Type}, {"ForWeek", type datetime}, {"Currrent Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"AllData", each _, type table}}),
    SelectLatest = (Group as table) as table => Table.SelectRows(Group, let latest = List.Max(Group[ForWeek]) in each [ForWeek] = latest),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "LatestData", each SelectLatest([AllData])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllData"}),
    #"Expanded LatestData" = Table.ExpandTableColumn(#"Removed Columns", "LatestData", {"Stage", "status ", "ForWeek", "Currrent Status"}, {"Stage", "status ", "ForWeek", "Currrent Status"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded LatestData",{"Stage", "status ", "id", "ForWeek", "Currrent Status"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Stage", type text}, {"status ", type text}, {"id", Int64.Type}, {"ForWeek", type date}, {"Currrent Status", type text}})
in
    #"Changed Type1"