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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AndySmith
Helper II
Helper II

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.


Click here and Go to My LinkedIn Page


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.


Click here and Go to My LinkedIn Page


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

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.


Click here and Go to My LinkedIn Page


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.


Click here and Go to My LinkedIn Page


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.


Click here and Go to My LinkedIn Page


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors