Revenue Organic Vs New Customer

[Anonymous]

Hi All, 

I'm trying to work out Revenue split between Organic V New Customer. 

So it would be Revenue this month Aug20 split

Organic definition

- if they had any rev BEFORE the 12 months from Aug 20 then they are ‘organic’ (ie if its current month Aug 20, then go back to Aug19 and look at the 12 months from this point...ie 24 months before now being the starting point)

New Customer
- if they didn't have any rev in that period Sept18 to Aug19, then the rev they have in Aug20 is new business

Any Advice would be greatly appreciated!

Peter

[v-lili6-msft]

hi @Anonymous 

Try to create two rolling date calculation with if conditional see if it is blank to determine whether is Organic or New Customer.

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

if you still have the problem ,please share your sample pbix file and your expected output.

Regards,

Lin

[amitchandak]

@Anonymous , With a date table

examples

Rolling 12 = CALCULATE([Sales],DATESINPERIOD('Date'[Date],ENDOFMONTH('Date'[Date]),-12,MONTH))
Rolling 12 before 12 before three = CALCULATE([Sales],DATESINPERIOD('Date'[Date],eomonth(max('Date'[Date]),-12),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
Appreciate your Kudos.