Forum Discussion

RaquelSantos_PT's avatar
RaquelSantos_PT
New Member
4 years ago

Calculate the previous segment

Hello,

 

I have a model with a RFM Segmentation, that is calculate for the period select.

However, now I would like to select a period, and calculate the last segment for those customers, what I want to say is, I don't want purchases from selected period to influence the segment calculation.

I see 2 possible solutions:

- freeze the costumers selected and see their behavior on last 12 months (before the minimum date of selected period)

- calculate the segments for each month, and add a new slicer related to the segmentation month

 

However I don't know how I can implement none of these solutions.

 

Someone can help me on this problem? How do you will fix this problem?

 

Thank you so much,

Raquel

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    RaquelSantos_PT , if you need different values of RFM each month, then you need to create a column .

     

    New column

    var _mon = eomonth([Date],-13)+1

    Var _max = [Date]

    return

    last 12 month of sales = sumx(filter(table,[customer] =earlier([cutomer) && [Date] >=_min && [Date] <=_max),[Sales])

     

    This can be used in RFM

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

  • RaquelSantos_PT's avatar
    RaquelSantos_PT
    New Member
    4 years ago

    Hello amitchandak ,

     

    right now I calculate the RFM segment as a measure with the following code:

     

    Clientes_RFM =
    var vToday = max('calendar'[date])
    Var vResult = CALCULATE('Cliente PT'[# Clientes],
    filter(VALUES('Dados OMS PT'[email]),
    [# Encomendas]>=min(Aux_RFM_Freq[Lim_Inf]) &&
    [# Encomendas]<=max(Aux_RFM_Freq[Lim_Sup]) &&
    [Cesta]>min(Aux_RFM_Monetary[Lim_Inf]) &&
    [Cesta]<=max(Aux_RFM_Monetary[Lim_Sup]) &&
    DATEDIFF([Ultima_Compra],vToday,DAY)>=min(Aux_RMF_Recency[Lim_Inf]) &&
    DATEDIFF([Ultima_Compra],vToday,DAY)<=max(Aux_RMF_Recency[Lim_Sup])
    ))
    return vResult
     
    and I also have auxiliary tables to define the limits of the segments: 
     

     

    I didn't understand how can calculate the segment of each customer by month.

    Can you help me please?

     

    Thank you,

    Raquel