Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Time series problems

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?

Need Help
Tips and Tricks
Tutorial Requests
No Replies