Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
AndySmith
Helper III
Helper III

New Customer Calculations - Rolling 6 month definition

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?

 

2 ACCEPTED SOLUTIONS

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 )
 
Picture1.png
 
 
Did I answer your question? Mark my post as a solution, this will help others!

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

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.
 
Picture1.pngPicture2.png
 
 
Did I answer your question? Mark my post as a solution!

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

6 REPLIES 6
v-alq-msft
Community Support
Community Support

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:

c1.png

 

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
Helper III
Helper III

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?

 

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.
 
Picture1.pngPicture2.png
 
 
Did I answer your question? Mark my post as a solution!

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Jihwan_Kim
Super User
Super User

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!

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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... 

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 )
 
Picture1.png
 
 
Did I answer your question? Mark my post as a solution, this will help others!

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.