Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

RFM Analysis in a date range

I have a RFM table with RFM score calculated of the whole customer data, I want to calculate RFM score dynamically based on the last date(quarterly) specified from the slicer. So the R,F,M scores should be calculated upto the specified quarter date from the slicer.
Like I am making an RFM table from the master table called Cohort, RFM table has unique account ids while Cohort does not, I want to calculate the number of occrences ('F' Value) of a particular account id upto the specified quarter date from the Cohort table and respectively have a F score.

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi All
    Firstly  lbendlin thank you for your solution!
    And Anonymous to my understanding,y    ou want to filter dynamically in the calculation table by the date table, unfortunately, the calculation table will be calculated once at the time of creation, and it is difficult to be affected by the filter and slicer later, if you want to achieve dynamic filtering, it is recommended to operate in the Cohort table.

     

    F = 
VAR A=SELECTEDVALUE('Date'[Date],MAX('Date'[Date]))
VAR B=SELECTEDVALUE('Date'[Date],MIN('Date'[Date]))
RETURN
CALCULATE(COUNTROWS('Cohort'),'Cohort'[Date]>=B&&'Cohort'[Date]<=A,ALLEXCEPT(Cohort,Cohort[AccountID]))

     

    If you still have questions, check out the PBIX file I uploaded, I hope it helps!

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.