Need help with data modelling

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

Need Help

DiKi-I
1 year ago
@Ibendlin

Anonymous

1 year ago

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.