Report the latest or blank date value

[huguest]

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 Issue. 

The first table is an example of the table with Issues and Items (+ Completion Date), and I need help getting the Completion Date in the second table (without the Item).

Issue	Item	Completion Date
511	1	2017-01-01
512	1
513	1	2017-01-01
513	2	2017-01-31
514	1	2017-01-01
514	2

Issue	Completion Date
511	2017-01-01
512
513	2017-01-31
514

"Latest" gives me the date of the last completed item, but it doesn't quite work if I have issues that haven't been completed yet.  

If I have multiple items and one of them has not been completed yet, I need the date value for that issue to remain blank. 

Thanks.

[huguest]

@Zubair_Muhammad thank you for your help.  I tried it but got an error while trying to create the Table "A table of multiple values was supplied where a single value was expected"....

I tied another approach where I copied the date column and replaced the balnk dates with 2999-12-31.  That allows me to get those using "latest".

Hugues.

[MarcelBeug · Accepted solution]

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]

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

[Zubair_Muhammad]

@huguest