# Segment over time by Customer

Hi

How can I showcase a customers journey over time by sales grouping

eg Sales groupings
< 500
< 2000
<4000
<8000

8000

Customer A
first 11mths spend less than 500 on average therefore sits in group <500
in mth 12 they spend 5000 bringin the average to say £1249 therefore moving them to group <2000

The average would be calculated on a rolling 6mths

How can i
achieve this

@AliB

Please use this measure and let me know if it helps you for your scenario

You can apply in Matrix, table or visual of your choice

Sales R6Months =
VAR NumOfMonths = 6
VAR LastCurrentDate =
MAX ( Dates[Date] )
VAR Period =
DATESINPERIOD ( Dates[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
CALCULATE (
AVERAGEX (
VALUES ( Dates[MonthnYear] ),
[Total Sales]
),
Period
)
VAR FirstDateInPeriod = MINX ( Period, Dates[Date])
VAR LastDateWithSales = MAX ( Dates[Date])
RETURN
IF ( FirstDateInPeriod <= LastDateWithSales, Result )

Sales Group by R6M =
CALCULATE( SELECTEDVALUE( ‘Sales Banding’[Sales Grouping], BLANK() ),
FILTER( ALL(‘Sales Banding’),
[Sales R6Months] >= ‘Sales Banding’[Min] &&
[Sales R6Months] < ‘Sales Banding’[Max] ) )

Thank you Dhrubojit for your help - I think I’m getting there in my understanding. I have some questions about the variables

When I put this in a table it calculates the Sales R6Months up to Dec 21 even though there is no data past Sept 2021 - it looks like it calculates the average based on the number of months so not always 6mths

Is there a reason why the SalesData[SalesDate] column isn’t used in these variables and why isn’t MAXX used for the LastDateWithSales?

VAR LastCurrentDate = MAX ( Dates[Date])

VAR FirstDateInPeriod = MINX ( Period, Dates[Date])

VAR LastDateWithSales = MAX ( Dates[Date])

Thanks again for helping to further my understanding

@AliB

Please use this updated more precise measure (Notice change in LastCurrentDate variable)

Please test and let me know if it helps for your issue

Sales R6Months =
VAR NumOfMonths = 6
VAR LastCurrentDate =
LASTNONBLANK ( ‘Dates’[Date], [Total Sales] )
VAR Period =
DATESINPERIOD ( Dates[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
CALCULATE (
AVERAGEX (
VALUES ( Dates[MonthnYear] ),
[Total Sales]
),
Period
)
VAR FirstDateInPeriod = MINX ( Period, Dates[Date])
VAR LastDateWithSales = MAX ( Dates[Date])
RETURN
IF ( FirstDateInPeriod <= LastDateWithSales, Result )

Thanks for the super quick reply!

Can you explain what the LastDateWithSales var is doing please - I know it getting the last date but why is it using the Dates table and not the Period table as in the FirstDateInPeriod variable

@AliB I have initially written it to not display average when date > sales date which in the initial query didn’t worked as you noted as corner scenario . .

Now with the optimized code , you don’t need those variables . .

You can remove them . . you can use the below measure which is more clean . .

Sales R6Months_updated =
VAR NumOfMonths = 6
VAR LastCurrentDate =
LASTNONBLANK ( Dates[Date], [Total Sales] )
VAR Period =
DATESINPERIOD ( Dates[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
CALCULATE ( AVERAGEX ( VALUES ( Dates[MonthnYear] ), [Total Sales] ), Period )
RETURN
Result

AS YOU See in the screen shot below -both mesure (with or without those vars return same result)

Thank you.
If I want to calculate the R6Months for the previous Qtr
Is this the best way to do it - just altering the LastCurrentDate variable to

VAR LastCurrentDate = DATEADD( LASTNONBLANK ( Dates[Date], [Total Sales] ) -3, MONTH )

That above gives the correct result but I was wondering why the following didn’t work

CALCULATE ( [R6Months_updated], DATEADD( Dates[Date] , -3 , MONTH ) )

@AliB

If your requirement is met with above approach , May I request to mark this as a Solution as a token of appreciation for the contribution effort and time (I do accept Love as well as well )

I can surely talk about alterntive solution in another thread as there are always so many(multiple) but finite possible options and alternative solutions to approach same problem

You would certainly get same results with multiple solution approach to exact same problem

1 Like