I have a scenario as follows... There is a long list of sales of a company. I need to categorize all clients as their frequency of purchases. An active client is someone who purchase something in the last 6 months. I need to put up a chart that shows the number of active clients within the last 6 months every month.
Now, the problem: The chart is segmented in the last 6 months. I'm using 2 standard calendars tables so it brings the last 6 months of a reference date, using the following measure:
Ativo =
var vNPeriodo = ParamMeses[Valor ParamMeses]*30
var vDataSelecionada =
CALCULATE(
max('Calendário_Seleção'[Início do Mês]),
all('Calendário')
)
var vResultado =
if(max('Calendário'[Início do Mês]) > vDataSelecionada - vNPeriodo && max('Calendário'[Início do Mês]) <= vDataSelecionada, [Ativos], blank())
return vResultado
[Ativos] is the following measure:
Ativos = COUNTROWS(
Filter(
values(Base[Cliente])
,datediff([LastPurchase]),
Max('Calendário'[Start of the month],
Month)<=6))
The problem is when I put this meaure in a column chart.
Last say my last data is June. The chart will show the last 6 months (January - June).
In JUNE it should return me the number of active clients between January and June (and not only June as it is happening).... and in May, I expect the number of active clients between dec/22 and may/23... and so on.
Does anyone have a idea for that?
