topic Re: Measure to show current, new, recovered and lost buyers of the quarter in DAX Commands and Tips

Measure to show current, new, recovered and lost buyers of the quarter

Laocsulak — Wed, 01 Feb 2023 19:48:47 GMT

Hello,

I have created the next table to explain the measures I need to create:

I need to create the next 5 measures, where I mention the value that it would take if I would be filtering the quarter 4.

Buyers of the current quarter: 5. Because there were 5 different buyers in the quarter 4: buyers 1, 2, 3, 4 and 7

New Buyers of the quarter: 1. Because there was only one new buyer in the quarter 4: buyer 7

Buyers of the previous quarter: 3. Because there were 3 different buyers in the quarter 4: buyers 4, 5 and 6

Recovered buyers of the quarter: 3. This express the buyers that bought in the current quarter, and they didn't buy in the previous quarter, but they have bought in some previous quarter. These are the buyers 1, 2 and 3 who bought in quarter 2, not in quarter 3, and bought again in quarter 4.

Lost buyers of the current quarter: 2. This express the buyers that bought in the last quarter, but not in the current quarter. These are the buyers 5 and 6 that bought in quarter 3 but not in quarter 4.

I have created this model where I could create the Buyers of the current quarter and the New Buyers of the quarter measures, but not the last three:

https://drive.google.com/drive/folders/1A1lQk9UhC25q1rBgD-dIo1kdEi1FfDbL?usp=sharing

What's the best way to create these measures? Thanks in advance!

Re: Measure to show current, new, recovered and lost buyers of the quarter

amitchandak — Thu, 02 Feb 2023 02:18:58 GMT

Laocsulak , You have to follow the approach of customer retention

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention:https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458

You just have to change period

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Re: Measure to show current, new, recovered and lost buyers of the quarter

Anonymous — Thu, 02 Feb 2023 07:31:57 GMT

Hi Laocsulak

You can refer to the following measures.

Buyers of the previous quarter = CALCULATE(DISTINCTCOUNT(transactions[payer_id]),ALL(transactions),transactions[quarter]=MAX([quarter])-1) Lost buyers of the current quarter = var _lastquartercustomer=SUMMARIZE(FILTER(ALL(transactions),[quarter]=MAX([quarter])-1),[payer_id]) return COUNTROWS(FILTER(_lastquartercustomer,[payer_id] in VALUES(transactions[payer_id])=FALSE())) Recovered buyers of the quarter = var _lastquartercustomer=SUMMARIZE(FILTER(ALL(transactions),[quarter]=MAX([quarter])-1),[payer_id]) return CALCULATE(DISTINCTCOUNT(transactions[payer_id]),FILTER(transactions,transactions[trx_merchant_asc]<>1&&[payer_id] in _lastquartercustomer =FALSE()))

Best Regards!

Yolo Zhu

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

Re: Measure to show current, new, recovered and lost buyers of the quarter

Laocsulak — Thu, 02 Feb 2023 21:01:55 GMT

It worked, thanks!