Latest entry in the report

[PC2022]

I have multiple "planned activities" entries in my Dataverse (Project for the Web). On the report i need to indicate the latest entry. How would i do it?

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but if you want to approach to this in Power Query Editor to create [latest_flag] column, one of ways is to try something like below.

Please check the below picture and the attached pbix file.

let
    Source = project_source,
    #"Sorted Rows" = Table.Sort(Source,{{"reportingdate", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"group"}, {{"latestreportdate", each List.Max([reportingdate]), type nullable date}}),
    Custom1 = Source,
    #"Merged Queries" = Table.NestedJoin(Custom1, {"group"}, #"Grouped Rows", {"group"}, "Custom1", JoinKind.Inner),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Custom1", {"latestreportdate"}, {"Custom1.latestreportdate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Custom1", "latest_flag", each if [reportingdate] = [Custom1.latestreportdate] then 1 else 0),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"group", "plannedactivities", "reportingdate", "latest_flag"})
in
    #"Removed Other Columns"

Or, if you want to approach to this problem by creating calcualted column, please check the below picture and the same attached pbix file.

latest_flag CC =
VAR _group = project_source[group]
VAR _reportdate = project_source[reportingdate]
VAR _flagtable =
    SELECTCOLUMNS (
        FILTER (
            ADDCOLUMNS (
                project_source,
                "@RankByLatest",
                    RANK (
                        SKIP,
                        project_source,
                        ORDERBY ( project_source[reportingdate], DESC ),
                        ,
                        PARTITIONBY ( project_source[group] ),
                        MATCHBY (
                            project_source[group],
                            project_source[plannedactivities],
                            project_source[reportingdate]
                        )
                    )
            ),
            [@RankByLatest] = 1
        ),
        "@group", project_source[group],
        "@latestdate", project_source[reportingdate]
    )
RETURN
    COUNTROWS (
        FILTER ( _flagtable, [@group] = _group && [@latestdate] = _reportdate )
    )

[FreemanZ · Accepted solution]

hi @PC2022 ,

try like:

measure =

MAXX(

    TOPN(1, data, data[date]),

    data[plannedactivities]

)

[PC2022]

This worked! Thank you!