CODEWITHSUNDEEP
powerbi
haoran zhang
haoran zhang

Reputation: 51

Dynamic select latest date column in Power BI

There is a scenario as attached excel file, an issue progress daily tracker. In Power BI, if I use a table visual, and want to only show the latest update(in this case will be 11/30/2018), how could I let it be done auto instead of selecting the date column manually?enter image description here

Upvotes: 0

Views: 996

Answers (1)

Olly
Olly

Reputation: 7891

You'd be better unpivoting that source data and storing the date as a value of a "date" column, rather than having separate columns for each date. Then you can use all the DAX time intelligence functionality.

You could use a query like this, to normalise your source data:

let
    Source = MyTable,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"#", "Issue", "Owner"}, "Date", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"#", Int64.Type}, {"Issue", type text}, {"Owner", type text}, {"Value", type text}, {"Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Value] <> null and [Value] <> "")
in
    #"Filtered Rows"

Now your table view can be filtered using the Date field - if you want the latest date, then a Top 1 by Latest Date filter would work:

Screenshot of table visual with date filter

Upvotes: 1

Related Questions