NBTO - New Customer Sales (As per start date)

[gauravnarchal]

Sorry, I did not get the answer from my previous post so reporting this again.

I have an issue showing the NBTO sale (New Business Turn Over) visual in Power BI. A customer is a new customer until 12 months since it started and after 12 months it falls in the existing customer bucket.  Any invoice raised before the start date should not be calculated as NBTO and any Invoices raised after 12 months of the Invoice date should be in Existing Customer Bucker.

Invoice raised within 12 months from the start date is to be considered as NBTO.

For Example:-

Client “Better Leather” start date is 1st August’20. So this client will be NBTO from 1st September’20 until 31st August’21.

Now the matrix visual should show as:-

• When I select the Fiscal Year 2020 (Slicer) total sales for Sep’20 to Jun’21 should show as month-wise.
• When I select the Fiscal Year 2021 (Slicer) total sales for Jul’21 to Aug’21 should show as month-wise.

I have attached the PBIX file for your review. Click Here

[v-jayw-msft · Accepted solution]

Hi @gauravnarchal ,

You don't need to create the "ISNBTO" column. You could directly get the amount by using below formula.

NBTO_amount =

var _ProNo = CALCULATE(MAX(ClientGroupMaster[ProfileNumber]),FILTER(ClientGroupMaster,ClientGroupMaster[ProfileName]=SELECTEDVALUE(ClientGroupMaster[ProfileName])))

var _start_date = CALCULATE(MAX(ClientGroupMaster[Contract Start Date]),FILTER(ClientGroupMaster,ClientGroupMaster[ProfileName]=SELECTEDVALUE(ClientGroupMaster[ProfileName])))

return

CALCULATE(SUM(AR_Invoices[InvoiceAmount]),FILTER(AR_Invoices,AR_Invoices[ProfileNumber]=_ProNo&&AR_Invoices[InvoiceDate]>_start_date&&AR_Invoices[InvoiceDate]<EDATE(_start_date,12)))

Best Regards,

Jay

[ValtteriN]

Hi,

You can create a category measure and then use this to calculate the revenue based on customer category.


E.g. [Category] = SWITCH(TRUE(),condition1, category1,condition2,category2)
[New Customer Sales] = SUMX(Table,CALCULATE(SUM(Table[Sales]),[Category]="New Customer")

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[gauravnarchal]

Hello @ValtteriN  I can create the column as you suggested, but before that, I would like to check from where is the Contract Start Date taken into consideration?  A customer is a new customer until 12 months since it started (As per the start date).

For Example:-

Client “Better Leather” start date is 1st August’20 (Refer -  ClientGroupMaster Table). So this client will be NBTO only from 1st September’20 until 31st August’21.

I am sorry if I missed anything and thank you for looking into this.

[ValtteriN]

Hi,

My suggestion is that you create a measure which categorises customers. So then contract start would be included in e.g. condition1