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
Grouping Segmenting -Upload.pbix (710.4 KB)
achieve this
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
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
Went outside replying . .
@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 . .
Please check for yourself and let me know your feedback
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 ) )
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
Thanks for all your help
@AliB I am trying to answer your next question; that you posted in same thread.
I don’t want to conflate the same theread the , the next question although related ; but is a different ask then the initial question on this thread
@AliB
Can you post a New form therad (question) -on your related question that you are Seeking help .
Please attach your pbix file with the measure you are saying is not working . .
For example you mentioned that why measure with calculate is not working , Can you pls give the full measure , PBIX to that I can access precisely ?
