Days since last purchase grouping

In this example, the goal is to group (bin) customers based on the number of days since they last purchased.

  1. 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 ...

Get Microsoft Power BI Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.