NBTO - New Customer Sales (As per start date) and Prior Year (Same Period)

[gauravnarchal]

I want to create NBTO (New Business Turn Over) report in Power BI.  Can you please suggest any template or advise any measures that can handle this requirement.

A customer is a new customer until 12 months since it started and after 12 months it falls in existing customer bucket

My requirement = Total sale of New customer (As per the start date) v/s Prior Year (Same Period)

[v-alq-msft]

Hi, @gauravnarchal 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create measures as below.

New customer Sales = 
var tab = 
ADDCOLUMNS(
    'Table',
    "Flag1",
    IF(
        DATEDIFF([Start Date],TODAY(),DAY)<=365,
        1,0
    )
)
return
SUMX(
        FILTER(
            tab,
            [Flag1]=1
        ),
        [Sales]
)

New customer sales last year = 
var tab = 
ADDCOLUMNS(
    'Table',
    "Flag2",
    IF(
        DATEDIFF([Start Date],DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),DAY)<=365,
        1,0
    )
)
return
    SUMX(
        FILTER(
            tab,
            [Flag2]=1
        ),
        [Sales]
    )

Result = 
var tab = 
ADDCOLUMNS(
    'Table',
    "Flag1",
    IF(
        DATEDIFF([Start Date],TODAY(),DAY)<=365,
        1,0
    ),
    "Flag2",
    IF(
        DATEDIFF([Start Date],TODAY()-365,DAY)<=365,
        1,0
    )
)
return
DIVIDE(
    SUMX(
        FILTER(
            tab,
            [Flag1]=1
        ),
        [Sales]
    ),
    SUMX(
        FILTER(
            tab,
            [Flag2]=1
        ),
        [Sales]
    )
)

Today is 7/20/2020. Here is the result:

Best Regard

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gauravnarchal]

@v-alq-msft 

Thank you for your response

If you see the below table

1. If I want to see NBTO & existing customer sale from the selected month of Feb’20. So any client which started the first sale in/after Mar’19 should come under NBTO and all other clients that started before Mar’19 should be under “existing client”.
2. Any customer who has not done any sale in the last 6 months from selected date which is Feb’20 comes under lost customer.

Further

1. If I wanted to see NBTO & existing customer sale from the selected month of Jun’20. So any client which started first sale in/after Jul’19 should come under NBTO and all other client started before Jul’19 should be under existing client.
2. Any customer who has not done any sale in the last 6 months from selected date which is Jul’19 comes under lost customer.

Many thanks for your help

Here is the PBIX link - 

https://wetransfer.com/downloads/5fb5422ebf69f21e0ce0daab09bfc3b620200720104017/9ecc44667422af12d93e...

[gauravnarchal]

Do you think above is achievable in Power BI?

[v-alq-msft · Accepted solution]

Hi, @gauravnarchal 

If you want to calculate by a selected date, you may create a Calendar table. The pbix file is attached in the end.

Then you may create measures as below.

lost customer sales = 
var tab = 
ADDCOLUMNS(
    'Table',
    "Flag1",
    IF(
        OR(
           DATEDIFF([Start Date],SELECTEDVALUE('Calendar'[Date]),MONTH)>6,
           DATEDIFF([Start Date],SELECTEDVALUE('Calendar'[Date]),MONTH)=6&&DAY([Start Date])<DAY(SELECTEDVALUE('Calendar'[Date]))
        ),
        1,0
    )
)
return
IF(
    HASONEVALUE('Calendar'[Date]),
    SUMX(
            FILTER(
                tab,
                [Flag1]=1
            ),
            [Sales]
    )
)

New customer Sales = 
var tab = 
ADDCOLUMNS(
    'Table',
    "Flag1",
    IF(
        DATEDIFF([Start Date],SELECTEDVALUE('Calendar'[Date]),DAY)<=365,
        1,0
    )
)
return
IF(
    HASONEVALUE('Calendar'[Date]),
    SUMX(
            FILTER(
                tab,
                [Flag1]=1
            ),
            [Sales]
    )
)

New customer sales last year = 
var _date = SELECTEDVALUE('Calendar'[Date])
var tab = 
ADDCOLUMNS(
    'Table',
    "Flag2",
    IF(
        DATEDIFF([Start Date],DATE(YEAR(_date)-1,MONTH(_date),DAY(_date)),DAY)<=365,
        1,0
    )
)
return
IF(
    HASONEVALUE('Calendar'[Date]),
    SUMX(
        FILTER(
            tab,
            [Flag2]=1
        ),
        [Sales]
    )
)

Result = 
var _date = SELECTEDVALUE('Calendar'[Date])
var tab = 
ADDCOLUMNS(
    'Table',
    "Flag1",
    IF(
        DATEDIFF([Start Date],_date,DAY)<=365,
        1,0
    ),
    "Flag2",
    IF(
        DATEDIFF([Start Date],DATE(YEAR(_date)-1,MONTH(_date),DAY(_date)),DAY)<=365,
        1,0
    )
)
return
IF(
    HASONEVALUE('Calendar'[Date]),
    DIVIDE(
        SUMX(
            FILTER(
                tab,
                [Flag1]=1
            ),
            [Sales]
        ),
        SUMX(
            FILTER(
                tab,
                [Flag2]=1
            ),
            [Sales]
        )
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-alq-msft]

Hi, @gauravnarchal 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[AllisonKennedy]

You may start by determining the first purchase date for each customer. See this post for getting product information and adapt for your data:
https://blog.enterprisedna.co/discover-what-was-the-initial-product-a-customer-purchased-using-dax/

Then use that date inside a measure with DATEDIFF to filter only for customers with less than 1 year or 365 days difference between TODAY and FirstPurchaseDate