Propagation of customer level measure to the order level
I have an order table and based on the creation date of the order I am trying to calculate the count of new customer/returning customers and revenue created by both segment. Is it a bit more complicated because I want to have it dynamic based on the period that a user choses. (Attached is the power bi notebook with my failed attempts to calculate this :))
Here is the data example I use:
Desired result & explanation: Let's say I want to see number of new/returning customers only for 01.01.2020. Based on the example above, customer A did its first order (A1) on 01.01.2020, hence customer A is a new customer for the given period (01.01.2020). Customer B did his first order (B1) on 23.09.19 and the second order (B2) was done on 01.01.202, hence, customer B is a returning customer for the given period. The revnue generated by new customer and returning customer for the period of 01.01.2020 is: new customer: 60 (A1) and returning customer: 10 (B2).
If I do the same calculations for the whole month of Janury for 2020, it will be like this:
2 (B & C)
70 (B2+B3 +C2)
My logic to calculate this was the following: As a have a date slicer, I count number of orders before the latest date of the slicer and number of orders that were placed between the dates specified in the slicer. If those two numbers are equal, then it is a new customer.