Forum Discussion
Average Days between Purchases
There is no need to do this via the advanced editor, all can be done through the UI. It's just that you might be able to study the steps if you past the code as it is into the advanced editor.
Have a look at the steps in this video:
You can do this in the query editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZczBDcAgDATBXu6NkM8QJ9Ri0X8b4F8sf0fadceLBkoXdhUadnPwkqxEVmkEfTW0RE/QTDSDtL4yaRDr/kf7AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"CUSTOMER ID" = _t, DATE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CUSTOMER ID", type text}, {"DATE", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"CUSTOMER ID"}, {{"First", each List.Min([DATE]), type date}, {"Last", each List.Max([DATE]), type date}, {"All", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "AverageTimeBetween", each if Table.RowCount([All])>1 then Number.From([Last]-[First])/(Table.RowCount([All])-1) else ""),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"})
in
#"Removed Columns"Pls let me know if you need help implementing this code.
Hi ImkeF, thanks so much for your help!
Are you aware of any way to do this in DAX? I don't have a strong enough background to adapt this code to my dataset in the advanced editor, I'm still very much a newbie.
There is no need to do this via the advanced editor, all can be done through the UI. It's just that you might be able to study the steps if you past the code as it is into the advanced editor.
Have a look at the steps in this video:
Hi Imke, that worked like a charm! In hindsight I made it much more complicated than it had to be, but I've been fiddling with PowerBI only a few days. Thanks so much for taking the time to explain it, I learned a little more today :)
