cancel
Showing results 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

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 (\$)

2 ACCEPTED SOLUTIONS
Super User

Hi, @AndySmith

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 )

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.

Super User

Hi, @AndySmith

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

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.

6 REPLIES 6
Community Support

Hi, @AndySmith

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

Calendar:

``````Calendar =
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.

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?

Super User

Hi, @AndySmith

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

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.

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.

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.

Helper II

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

Super User

Hi, @AndySmith

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 )

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors