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

[amangsk]

I am working with a Power BI dataset where I need to create a Matrix Visualization. The rows of the matrix should show Statements, the columns should represent Stages, and the values should show the count of accounts for the latest stage of each Statement within the selected date range. means Date range user can select from and to date

Key Details:

Dataset Structure:

The dataset contains the following columns:

Account ID

Statement

Date

Stage Number

Goal:

For each unique combination of Account and Statement, I want to:

Identify the latest date within the selected date range.

Use the Stage Number associated with this latest date to populate the count in the matrix.

The matrix should aggregate this count by Statement (rows) and Stages (columns).

Expected Output:

A matrix that looks like this:

Statement        Stage 1               Stage 2               Stage 3               Stage 4

Statement A    2             1             0             1

Statement B    1             0             2             0

Total     3             1             2             1

Here:

Each number represents the count of accounts in the corresponding stage for the latest date.

Example Dataset

Account ID       Statement        Date     Stage Number

A1          Statement A    1/1/2024           1

A1          Statement A    1/10/2024        2

A2          Statement B    1/5/2024           3

A3          Statement A    1/15/2024        1

A3          Statement A    1/20/2024        4

A4          Statement B    1/18/2024        3

Expected Matrix Output:

Statement        Stage 1               Stage 2               Stage 3               Stage 4

Statement A    1             1             0             1

Statement B    0             0             2             0

Total     1             1             2             1

When selected date range of 2024-01-01 to 2024-01-05

Expected Matrix Output

Statement        Stage 1               Stage 2               Stage 3               Stage 4

Statement A    1             0             0             0

Statement B    0             0             1             0

Total     1             0             1             0

[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"