Forum Discussion

huguest's avatar
huguest
Icon for Advocate II rankAdvocate II
8 years ago
Solved

Report the latest or blank date value

Hello,   I have top level issues that can have one or more items underneath it.  I am trying to report the completion date for each Issue that represents the completion of the latest item for that ...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    A Power Query solution:

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Issue", Int64.Type}, {"Item", Int64.Type}, {"Completion Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Issue"}, {{"Completion Date", each Table.Max(_,"Item")[Completion Date], Value.Type(#"Changed Type")}})
in
    #"Grouped Rows"
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Hi huguest

 

Please try this

 

Go to Modelling TAB>>>> NEW TABLE .......Then Enter this formula

 

New Table =
SUMMARIZE (
    TableName,
    TableName[Issue],
    "Completion Date",
    VAR mymax =
        CALCULATE ( MAX ( TableName[Item] ), ALLEXCEPT ( TableName, TableName[Issue] ) )
    RETURN
        CALCULATE (
            VALUES ( TableName[Completion Date] ),
            FILTER ( VALUES ( TableName ), TableName[Item] = mymax )
        )
)