In this example, the goal is to group (bin) customers based on the number of days since they last purchased.
- Create a new M query in Power BI Desktop that groups the customer keys by their last order date and computes the date difference between this order date and the current date:
let Source = AdWorksProd, ISales = Source{[Schema = "BI", Item = "vFact_InternetSales"]}[Data], CurrentDate = DateTime.Date(DateTime.LocalNow()), CustomerGrouping = Table.Group(ISales, {"CustomerKey"}, {{"Last Order Date", each List.Max([Order Date]), type date}}), DaysSinceLastPurchase = Table.AddColumn(CustomerGrouping, "Days Since Last Purchase", each Duration.Days(CurrentDate - [Last Order Date]), Int64.Type)in DaysSinceLastPurchase ...