Kohort Analysis tip request

[KimStahl94]

Hi there,

I am trying to model a simple kohort analysis and I am facing some issues. I want to display the course of absolute customer count and percentage customer count by time. The latter one is showing <100% for the 2017 kohort in the actual year 2017 (same problem within the 2014, 2015 and 2019 kohorts). I am working with a site filter to only include invoices that are payed. The correct customer count is 142 and my measures are the following:

n Customer = DISTINCTCOUNT ( Invoices[Customer_oid] )

n Customer Retention = 
VAR CurrentYear = SELECTEDVALUE ( Invoices[Year of first Invoice] )
VAR CurrentKohort = SELECTEDVALUE ( Kohorts[Value] )
RETURN
CALCULATE (
    [n Customer] ,
    FILTER ( 
        Invoices ,
        YEAR ( Invoices[date] ) = CurrentKohort ) )

% Customer Retention = 
VAR CurrentYear = SELECTEDVALUE ( Invoices[Year of first Invoice] )
VAR CurrentKohort = SELECTEDVALUE ( Kohorts[Value] )
RETURN
DIVIDE ( 
    CALCULATE (
        [n Customer] ,
        FILTER ( 
           Invoices ,
           YEAR ( Invoices[date] ) = CurrentKohort ) ) , 
        [n Customer] , BLANK () )

But my measure [n customer] calculates 143 customers in my 2014 kohort. I figured that there is one customer within "invoiceCompleted" and 2 customers within "invoiceCanceleld" in kohort 2017 which could cause that error. But my filter settings should be fine:

I also want to highlight my matrix which works fine for the absolute customer count, but won't work for my percentage measure and I need to visualize both measures in the same matrix. Therefore I use and nested If-Statement:

Switch Measure = 
IF (
    SELECTEDVALUE ('Switch Measure'[Value] ) = "n Customer Retention" , [n Customer Retention]  ,
    IF (
        SELECTEDVALUE ('Switch Measure'[Value] ) = "% Customer Retention" , FORMAT ( [% Customer Retention] , "#%" ),
        BLANK () ) )

Thanks in advance for any tips and ideas!
Regards
KS

[KimStahl94]

Hi,

you can find sample data here.

Regards
KS

[lbendlin · Accepted solution]

Thank you for providing the sample file.

I am working with a site filter to only include invoices that are payed. 

You neglected to implement that filter in the "Year of first Invoice" calculated column.

Year of first Invoice = 
CALCULATE (
    YEAR ( MIN ( Invoices[date] ) ) ,
    ALLEXCEPT (
        Invoices ,
        Invoices[Customer_oid] )
    ,Invoices[Status] in {"InvoicePartlyPayed","InvoicePayed"}    )

The background color coding doesn't work because your "% Customer Retention" measure yields a string. You would need to create another measure that computes a color value for both scenarios. 
Color coding with DAX - Microsoft Power BI Community

[KimStahl94]

Hi,

thank you very much!
Regards
KS

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.