DAX calculate Previous month lost customer revenue

[APIR]

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

[APIR]

up

[v-kaiyue-msft]

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.

[APIR]

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

[APIR]

up