RFM Analysis in a date range

[Anonymous]

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.

[Anonymous]

Hi All
Firstly  lbendlin thank you for your solution!
And @Anonymous to my understanding,you 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.

[lbendlin]

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-...

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/1447...