topic Re: DAX calculate Previous month lost customer revenue in DAX Commands and Tips

DAX calculate Previous month lost customer revenue

APIR — Wed, 26 Jun 2024 21:31:17 GMT

Hi guys,

I am having trouble calculating the previous month lost customer revenue.

My model relies on a single way relationship between thr fact table and the time table, so that is not possible to change.

I would like to display the measure "Churn_lost_$" as shown in the picture below.

Thank you very much! 🤙

You can donload the model here:

https://drive.google.com/file/d/1MKFmtBIm1Z-yJ0--FhVRuIrZqWVV166c/view?usp=sharing

Re: DAX calculate Previous month lost customer revenue

APIR — Wed, 26 Jun 2024 07:08:13 GMT

Re: DAX calculate Previous month lost customer revenue

Anonymous — Wed, 26 Jun 2024 09:05:27 GMT

Hi APIR ,

Create measure.

Step1 = var _column= SELECTCOLUMNS( FILTER(ALL('FACT_Orders'), 'FACT_Orders'[Account Name 1]=MAX('Accounts'[Customer])),"Test",'FACT_Orders'[Date]) return IF( MAX('TIME_Table'[Date]) in _column,BLANK(),0)

Step2 = COUNTX(ALL('Accounts'),'Accounts'[Customer])

Step3 = IF( HASONEVALUE('Accounts'[Customer]) , [Step1], IF( HASONEVALUE('TIME_Table'[Date].[Month]) && [Step2]<>[Accounts Distinct],0,BLANK()))

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

Re: DAX calculate Previous month lost customer revenue

APIR — Wed, 26 Jun 2024 21:30:48 GMT

Hello Clara,

First of all thank you for your time. Not correct though (yet!)

I've increased the data and the value are incorrect. Here is how I imagine the result:

I've tried many things but I still stuck.

Here is the file with more data:

https://drive.google.com/file/d/1MKFmtBIm1Z-yJ0--FhVRuIrZqWVV166c/view?usp=sharing

Re: DAX calculate Previous month lost customer revenue

APIR — Fri, 28 Jun 2024 14:49:17 GMT

Re: DAX calculate Previous month lost customer revenue

APIR — Mon, 01 Jul 2024 09:00:50 GMT

Re: DAX calculate Previous month lost customer revenue

APIR — Tue, 02 Jul 2024 16:17:49 GMT

Re: DAX calculate Previous month lost customer revenue

APIR — Wed, 03 Jul 2024 13:59:02 GMT

Re: DAX calculate Previous month lost customer revenue

APIR — Wed, 03 Jul 2024 22:49:59 GMT

Re: DAX calculate Previous month lost customer revenue

APIR — Sat, 06 Jul 2024 09:34:50 GMT