Getting the numbers of days between tow dates by name and get the average too

[FARIDMD]

Hi, I need assistance from expert here.

I have a data of request item which include (Name, the date of the request)   and want to find the number of days between each request by each name (cover in blue) and later find the average of the days different.

Thank you 🙂

[Anonymous · Accepted solution]

Hi @FARIDMD ,

I suggest you to do some transformation on your table and then calculate the result you want.

Old Table:

New Table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWMjIwMtI3sNQ3MgdznRCChob6hsZIXCMY1xlJ0ACoF8Y11jcAKUPimukbGSJxLfUNgCbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Name], "/") then "" else [Name]),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down","",null,Replacer.ReplaceValue,{"Custom"}),
    #"Filled Down1" = Table.FillDown(#"Replaced Value",{"Custom"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Filled Down1", "Date", each if [Name] = [Custom] then "" else [Name]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Custom", type text}, {"Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Date] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Name"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Name"}, {{"Rows", each _, type table [Name=nullable text, Date=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Index", each Table.AddIndexColumn([Rows],"Index",1)),
    #"Expanded Index" = Table.ExpandTableColumn(#"Added Custom", "Index", {"Date", "Index"}, {"Index.Date", "Index.Index"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Index",{{"Index.Date", "Date"}, {"Index.Index", "Index"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"Rows"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Date", type date}, {"Index", Int64.Type}})
in
    #"Changed Type2"

Add a calculated column in your new table by dax.

DateDiff =
DATEDIFF (
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Name] ),
            'Table'[Index] < EARLIER ( 'Table'[Index] )
        )
    ),
    'Table'[Date],
    DAY
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gaurav-lakhotia]

Hi @FARIDMD , could you share a sample file?

Do you want to calculate in a measure or column in the table will work?