latest Date and finding the 2nd Latest Date

[Anonymous]

Hi team,

I'm presently stuck with 2 things in a report am working presently and any suggestions will be of great help. I have a table like the one mentioned below and I would like to:

1. Compare the status of projects based on the latest update and the 2nd last update to indicate if there is an up/downward trend or static. e.g., Project 1 status has changed from 1 to 2 when compared with update on January 19th and February 2nd which are the lastest 2 updates in terms of date(s).

2. Ideally, I would like to indicate the up/downward or static trend using icon which I'm not sure. how to?

Date	Project	Status
1/5/17	PRJ1	1
1/19/17	PRJ1	1
2/2/17	PRJ1	2
1/5/17	PRJ2	2
1/19/17	PRJ2	3
2/2/17	PRJ2	1

Kind regards,

A!

[MarcelBeug · Accepted solution]

Power Query answer to your first question in this video resulting in the code below.

Adjust the Source to yours.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\latest Date and finding the 2nd Latest Date.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Date", type date}, {"Project", type text}, {"Status", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Project", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Project", "Status"}, {"Previous.Project", "Previous.Status"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Previous",{"Index", "Index.1"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Project"}, {{"MaxDate", each List.Max([Date]), type date}, {"AllData", each _, type table}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Date", "Status", "Previous.Project", "Previous.Status"}, {"Date", "Status", "Previous.Project", "Previous.Status"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded AllData", each ([Date] = [MaxDate])),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Trend", each if [Previous.Project] <> [Project] then "Static" else if [Status] > [Previous.Status] then "Up" else if [Status] = [Previous.Status] then "Static" else "Down"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "PrevStatus", each if [Project] = [Previous.Project] then [Previous.Status] else null),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"MaxDate", "Previous.Project", "Previous.Status"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Project", "Date", "PrevStatus", "Status", "Trend"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Project", type text}, {"Date", type date}, {"PrevStatus", Int64.Type}, {"Status", Int64.Type}, {"Trend", type text}})
in
    #"Changed Type1"

[Anonymous]

Thank you Marcel, I was able to resolve the long pending issue and was able to have this replicated in my reporting accordingly.

Really appreciate the time you took to have this recorded in a video exclusively for me and it feels special (:

Kind regards,

A!

[Anonymous]

Hi Marcel,

Thank you for sharing the code below and the video link for the same. I will have this tried this evening in the actual report and will revert.

Kind regards,

A!