topic Re: Group if created within 72 hours in DAX Commands and Tips

Group if created within 72 hours

GoblinKingdom — Wed, 02 Jun 2021 13:43:57 GMT

Hello,

I am attempting group my table of IncomingWork items by CustomerID as well as if they have been created within 72hours from the earliest entry. I have created the sample table with data below and how the items should be counted.

The end result should have CustomerID 1001 have a total of 2 items, and 2001 a total of 1 item.

I am not familiar enough with DAX functions to complete this, is anyone able to assist me?

Thank you in advance.

Re: Group if created within 72 hours

CNENFRNL — Wed, 02 Jun 2021 14:44:57 GMT

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndAxDsAgCEDRuzBrghhsO7bXMN7/Gg52qd/BdH0DfKhVbgliaimqx5SlhYlOkCnJt6iQOD5z/IrSRCXqoIcH/SAnlS06SBfofeGHWG/sWhFTjV2ZG1c0R4yvtg4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerID = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerID", type text}, {"Date", type date}}), #"Grouped Rows" = Table.RemoveColumns(Table.Group(#"Changed Type", {"CustomerID"}, {{"ar", each Table.AddIndexColumn(Table.Group(_, "Date", {"All", each _}, 0, (x,y) => Number.From(Duration.Days(y-x)>=3)),"Index",1)}}), "CustomerID"), #"Expanded ar" = Table.RemoveColumns(Table.ExpandTableColumn(#"Grouped Rows", "ar", {"Date", "All", "Index"}, {"Date", "All", "Index"}), "Date"), #"Expanded All" = Table.ExpandTableColumn(#"Expanded ar", "All", {"CustomerID", "Date"}, {"CustomerID", "Date"}) in #"Expanded All"

Re: Group if created within 72 hours

GoblinKingdom — Wed, 02 Jun 2021 16:14:41 GMT

Thank you for the quick reply. I apologize though, I meant to ask this for in a Measure format.