Determine Sales where customer join date < 12 months ago

[Anonymous]

Hi All,

I'd like to calculate roughly the following:

Calculate(SUM(Table[sales], 

Filter(Table, Table[NewClientDate] < 12 months ago. 

The goal is for this measure to show the number of sales by customers that joined in the past 12 months. It should be dynamic in the sense that if the date is January 2021, it should only include sales from customers that joined in January 2020 until then. 

Thanks in advance,

Justin

[ValtteriN]

Hi,

You can create this kind of logic by Calculating 12Months sales and then placing that in a IF condition. E.g. IF(AND([Sales 12M>0],[Sales ALL]=0),[Sales 12M],0)

So if there are sales within the last 12M and before that there isn't any Sales then display the Sales from last 12M.

For 12M calculations you can use this kind of pattern:

var selection_today = CALCULATE (
selectedmeasure(),
DATESBETWEEN (
'Calendar'[Date],
EDATE(TODAY(),-12)
,
today()
)
)

return
selection_today

This is a calculation group so you can replace SELECTEDMEASURE() with your [Sales].


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[Anonymous]

Hi @ValtteriN 

It's close, but not exactly what I'm looking for. In the calculation you propose, it only shows data for 2021 in my tables, because it compares the date to Today. I'd actually like it to be compared to the current month + year that is selected in my date. Something like the table below.

Hope this clarifies it! Thanks in advance,

Justin

Month	Sales	Sales (customer join date <12 months ago)
Jan 2021
Feb 2021
etc
Jan 2022

[ValtteriN · Accepted solution]

Hi,

For a more dynamic calcuation try this:

CALCULATE ( selectedmeasure(), DATESBETWEEN ( 'Calendar'[Date], DATEADD ( LASTDATE ( DATEADD ( 'Calendar'[Date], -12, MONTH ) ), +1, DAY ), LASTDATE ( 'Calendar'[Date] ) )  )