Need help with data modelling

[DiKi-I]

Hi ,
I need help with one of the the requirement. I have a csutomer churn table with csutomer and churn status and an invoice table.
For new and returning customer there is not any issue since they fall in the same fiscal year and can be easily sliced. 
The issue is with the lost customer. If a customer is lost in 2022 then it will have revenue in 2021 and not in 2022. But the client wants to see the figures as lost revenue in 2022 and not 2021. Could you please help how i can do this in power bi.

I have attached the sample pbix file with data. Please let me know for any questions.

https://drive.google.com/file/d/1ZdbyUWeARhVWBV8ncd47izKbhnR5MKY-/view?usp=drive_link

I have to show lost /new/returning in the bar chart as legend and the revenue for the different fiscal year. If a customer is lost in 2022 , in 2022 it should should show lost revenue(2021 revenue)

[DiKi-I · Accepted solution]

 @Ibendlin 

[DiKi-I]

Thank you so much it worked.

[Anonymous · Accepted solution]

Hello @DiKi-I 

You can refer to the following solution.

1.Create a new table. the table has no relationship with other tables.

ChurnType = VALUES(CustomerChurn[Churn])

2.Create a measure

MEASURE =
SWITCH (
    SELECTEDVALUE ( 'ChurnType'[Churn] ),
    "Lost",
        VAR a =
            CALCULATETABLE (
                VALUES ( CustomerChurn[ContactID] ),
                ALLSELECTED ( CustomerChurn[ContactID] ),
                CustomerChurn[FiscalYear] = SELECTEDVALUE ( 'Date'[FiscalYear] ),
                CustomerChurn[Churn] = "Lost"
            )
        RETURN
            CALCULATE (
                SUM ( Invoice[Amount] ),
                ALL ( Invoice ),
                Invoice[ContactID] IN a,
                Invoice[FiscalYear]
                    = SELECTEDVALUE ( 'Date'[FiscalYear] ) - 1
            ),
    CALCULATE (
        SUM ( Invoice[Amount] ),
        CustomerChurn[Churn] IN VALUES ( 'ChurnType'[Churn] )
    )
)

3.Then create a bar chart, and put the following field to the visual.

Output

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.

[DiKi-I]

@rajendraongole1 @danextian 

[DiKi-I · Accepted solution]

 @Ibendlin