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

FARIDMD — Mon, 02 Oct 2023 06:20:00 GMT

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 🙂

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

gaurav-lakhotia — Mon, 02 Oct 2023 07:44:30 GMT

Hi FARIDMD , could you share a sample file?

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

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

Anonymous — Wed, 04 Oct 2023 08:29:55 GMT

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.

topic Re: Getting the numbers of days between tow dates by name and get the average too in DAX Commands and Tips

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

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

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