New Customer Calculations - Rolling 6 month definition

[AndySmith]

Hi

I need to report on new customers within a sales table. However a new customer is not over all time, but defined as whether they made a purchase in the last 6 months? If they have not purchased in 6 month prior then they are new. 

The data is in a fairly standard fact table with dates,invoice numbers,customer, product detail, and sales amount ($)

Can anyone please assist?

[Jihwan_Kim · Accepted solution]

Hi, @AndySmith 

Thank you for your feedback.

Please kindly check the below picture and the pbix file link.

I hope I did not misunderstand your point.

The main DAX measure is below. And for others, you can check in the pbix file.

In the measure, I defined the new customers by using parameters.

For instance, in the PBIX file, you can select the number 6 to define the new customers as "not bought for last 6 months". Or you can change the slicer easily to the number 3 to change your definition of new customers.

The visualizations show customers count per month / new-customers count per month / quantity per month / new-customers-quantity per month. I hope you can easily apply this by using SUMX to multiply by the sales amount.

new customers count =

VAR

currentcustomers =

VALUES ( Data[Customer] )

VAR

monthsbefore =

SELECTEDVALUE ( 'Condition'[Condition] )

VAR

monthsperiod =

FILTER (

ALL ( dates ),

dates[MonthOffset]

>= SELECTEDVALUE ( dates[MonthOffset] ) - monthsbefore - 1

&& dates[MonthOffset]

<= SELECTEDVALUE ( dates[MonthOffset] ) - 1

)

VAR

previouscustomers =

CALCULATETABLE ( VALUES ( Data[Customer] ), monthsperiod )

VAR

newcustomers =

EXCEPT ( currentcustomers, previouscustomers )

RETURN

COUNTROWS ( newcustomers )

https://www.dropbox.com/s/42488f444dghfrr/AndySmith%202021%200323.pbix?dl=0 

Did I answer your question? Mark my post as a solution, this will help others!

[v-alq-msft]

Hi, @AndySmith 

You may create a calculated table as below. The pbix file is attached in the end.

Calendar:

Calendar = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "YearMonth",
    YEAR([Date])*100+MONTH([Date])
)

Then you could create a calculated column or a measure as below. There is no relationship between two tables.

Calculated column:

YearMonth = YEAR([Date])*100+MONTH([Date])

Measure:

Rolling 6 month count = 
var t =
SUMMARIZE(
    Sheet1,
    Sheet1[Customer],
    Sheet1[Product],
    Sheet1[YearMonth],
    "Flag",
    var l = 
    TOPN(
        6,
        CALCULATETABLE(
            DISTINCT('Calendar'[YearMonth]),
            FILTER(
                ALL('Calendar'),
                [YearMonth]<=YEAR(TODAY())*100+MONTH(TODAY())
            )
        ),
        [YearMonth]
    )
    return
    IF(
        [YearMonth] in l,
        1,0
    )
)
return
COALESCE(
    COUNTROWS(
        FILTER(
            t,
            [Flag]=1
        )
    ),0
)

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.

[AndySmith]

Hi

Thank you so much. This is an incredible tool and you put it together so quickly. 2 questions if I may:

1) what would the measure code be for new customers if we took away the parameter choice? Instead simply hard coded 6 months

2 How do I create a data set/flag to show these new customers and what they have purchased?

[Jihwan_Kim · Accepted solution]

Hi, @AndySmith 

Thank you for your feedback.

Related to your further questions,

 - Inside the below DAX measure, the red-colored words are related to the parameter-selection. If you change the red-colored words to a number that you need, the measure will be fixed to only show the result that is related to that number only. And, also you need to change the topic as well, because inside the topic "N" months is also influenced by the parameter-selection.

Inside the sample pbix file (link below), I changed to 6.

new customers count =

VAR

currentcustomers =

VALUES ( Data[Customer] )

VAR

monthsbefore =

SELECTEDVALUE ( 'Condition'[Condition] )

VAR

monthsperiod =

FILTER (

ALL ( dates ),

dates[MonthOffset]

>= SELECTEDVALUE ( dates[MonthOffset] ) - monthsbefore - 1

&& dates[MonthOffset]

<= SELECTEDVALUE ( dates[MonthOffset] ) - 1

)

VAR

previouscustomers =

CALCULATETABLE ( VALUES ( Data[Customer] ), monthsperiod )

VAR

newcustomers =

EXCEPT ( currentcustomers, previouscustomers )

RETURN

COUNTROWS ( newcustomers )

- Please check the below link and picture if I correctly understood what you need.

 If readers want to see Aug 2020 information, click that month from chart 1.

 If readers want to see the detail of Custoer AA, CTRL+ click Customer AA from chart 2.

 In the Power BI Service, no need to CTRL + click. But in the Power BI Desktop, if more than two are needed to be selected, CTRL+click is the way.

https://www.dropbox.com/s/42488f444dghfrr/AndySmith%202021%200323.pbix?dl=0 

Did I answer your question? Mark my post as a solution!

[Jihwan_Kim]

Hi, @AndySmith 

Thank you very much for your question.

It will be very much appreciated if I could have a look at your sample data.

Without looking into your data, if I am allowed to suggest,
- you are trying to create a this-month-new-customers-report, right?
- in a measure, create three virtual tables by using VAR.
- one is a VALUES(Customers[customer-name]) for this month.
- the other is VALUES(Customers[customer-name]) for the last 6 months.
- then, the final is, EXCEPT (first table, second table).
- The final table is the list of new customers who did not buy during the last six months, but who did buy in this month.
- by using the final table, you can create order quantity, sales amount, and so forth.

I hope it helped.

if you can share your sample data, please let me have a look, and I will try to create measures to find out who are new customers.

Thank you.

Did I answer your question? Mark my post as a solution!

[AndySmith]

Hi - appreciate the response. Yes corect - I need to measure new sales by month. One point to note is that it is by product - so I a new customer purchases Product A and Product B this will count as 2 new listings. Hope this makes sense

https://jovalgroup-my.sharepoint.com/:u:/g/personal/asmith_joval_com_au/EVpsFyx6l6FCpHLZSy7iI9kB2PuM... 

[Jihwan_Kim · Accepted solution]

Hi, @AndySmith 

Thank you for your feedback.

Please kindly check the below picture and the pbix file link.

I hope I did not misunderstand your point.

The main DAX measure is below. And for others, you can check in the pbix file.

In the measure, I defined the new customers by using parameters.

For instance, in the PBIX file, you can select the number 6 to define the new customers as "not bought for last 6 months". Or you can change the slicer easily to the number 3 to change your definition of new customers.

The visualizations show customers count per month / new-customers count per month / quantity per month / new-customers-quantity per month. I hope you can easily apply this by using SUMX to multiply by the sales amount.

new customers count =

VAR

currentcustomers =

VALUES ( Data[Customer] )

VAR

monthsbefore =

SELECTEDVALUE ( 'Condition'[Condition] )

VAR

monthsperiod =

FILTER (

ALL ( dates ),

dates[MonthOffset]

>= SELECTEDVALUE ( dates[MonthOffset] ) - monthsbefore - 1

&& dates[MonthOffset]

<= SELECTEDVALUE ( dates[MonthOffset] ) - 1

)

VAR

previouscustomers =

CALCULATETABLE ( VALUES ( Data[Customer] ), monthsperiod )

VAR

newcustomers =

EXCEPT ( currentcustomers, previouscustomers )

RETURN

COUNTROWS ( newcustomers )

https://www.dropbox.com/s/42488f444dghfrr/AndySmith%202021%200323.pbix?dl=0 

Did I answer your question? Mark my post as a solution, this will help others!