HI all,
I'm facing the problem with counting the number of repeating orders.
The main problem is that if the same customer bought product few times in current month, all his purchases except the first one should also be considered as repeated.
All I managed to do so far is to calculate how many orders are from those who bought anything in previous 5 months:
var datemin = DATEADD(STARTOFMONTH('Calendar'[Date]),-5,MONTH)
var datemax = DATEADD(startOFMONTH('Calendar'[Date]),-1,DAY)
var table1 = CALCULATETABLE(VALUES('Orders'[User_ID]),ALL('Orders'),DATESBETWEEN('Orders'[Date_payed],datemin,datemax))
var ordersrep = CALCULATE(DISTINCTCOUNT('Orders'[Order_ID]),YEAR('Orders'[Date_payed])=MAX('Calendar'[Year]),MONTH('Orders'[Date_payed])=MAX('Calendar'[Month]),'Orders'[User_ID] in (table1))
return
ordersrep
and repeated orders in current month:
var totalorders = CALCULATE(DISTINCTCOUNT('Orders'[Order_ID]),YEAR('Orders'[Date_payed])=MAX('Calendar'[Year]),MONTH('Orders'[Date_payed])=MAX('Calendar'[Month]))
var totalbuyers = CALCULATE(DISTINCTCOUNT('Orders'[User_ID]),YEAR('Orders'[Date_payed])=MAX('Calendar'[Year]),MONTH('Orders'[Date_payed])=MAX('Calendar'[Month]))
return
totalorders-totalbuyers
I can't just sum these two measures though as some orders are included in both. I think I should get the list, not the number, of each, unite it and return distinct count. Further I will need to calculate the sum of such orders But I failed to do so.
Any help?
