Calculate average maturity of customers

[Unknown]

I have a table with the dates of my customers' orders as represented in the picture bellow. If my customer doesn't put an order in a year period, then that customer will be lost, and the column "last" will be equal to 1. Column "first" will be equal to one, if a customer has its first order or if a lost customer returns.

I have another table where I want to calculate the average maturity of my customers each month. This table is composed by the columns in yellow in the picture bellow. I put here the other columns to explain how to get the values in the column "avg".
Customer's maturity is calculated by making the difference between the date in "year_month" column and the date of its first order. If a customer is lost, in the month where column last=1, the difference will be calculated between the date where the customer is lost and the date of its first order. If that customer returns, its maturity will reset.

I tried to replicate this behaviour, but was unable to. Can you help me, please?
Thankl you

[Anonymous]

Hi @Unknown ,

Please try below Dax formula:

A =
VAR cur_year_month =
    SELECTEDVALUE ( 'Table 2'[Year_month] )
VAR tmp1 =
    FILTER ( ALL ( 'Table' ), 'Table'[Customer] = "A" && 'Table'[First] = 1 )
VAR min_first_date =
    MINX ( tmp1, [Date] )
VAR max_first_date =
    MAXX ( tmp1, [Date] )
VAR last_date =
    CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Customer] = "A", 'Table'[Last] = 1 )
RETURN
    SWITCH (
        TRUE (),
        cur_year_month >= min_first_date
            && cur_year_month < last_date, DATEDIFF ( min_first_date, cur_year_month, DAY ),
        MONTH ( cur_year_month ) = MONTH ( last_date )
            && YEAR ( cur_year_month ) = YEAR ( last_date ), DATEDIFF ( min_first_date, last_date, DAY ),
        cur_year_month > max_first_date, DATEDIFF ( max_first_date, cur_year_month, DAY ),
        BLANK ()
    )

B =
VAR cur_year_month =
    SELECTEDVALUE ( 'Table 2'[Year_month] )
VAR tmp1 =
    FILTER ( ALL ( 'Table' ), 'Table'[Customer] = "B" && 'Table'[First] = 1 )
VAR min_first_date =
    MINX ( tmp1, [Date] )
VAR max_first_date =
    MAXX ( tmp1, [Date] )
VAR last_date =
    CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Customer] = "B", 'Table'[Last] = 1 )
RETURN
    SWITCH (
        TRUE (),
        cur_year_month >= min_first_date
            && cur_year_month < last_date, DATEDIFF ( min_first_date, cur_year_month, DAY ),
        MONTH ( cur_year_month ) = MONTH ( last_date )
            && YEAR ( cur_year_month ) = YEAR ( last_date ), DATEDIFF ( min_first_date, last_date, DAY ),
        cur_year_month > max_first_date, DATEDIFF ( max_first_date, cur_year_month, DAY ),
        BLANK ()
    )

C =
VAR cur_year_month =
    SELECTEDVALUE ( 'Table 2'[Year_month] )
VAR tmp1 =
    FILTER ( ALL ( 'Table' ), 'Table'[Customer] = "C" && 'Table'[First] = 1 )
VAR min_first_date =
    MINX ( tmp1, [Date] )
VAR max_first_date =
    MAXX ( tmp1, [Date] )
VAR last_date =
    CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Customer] = "C", 'Table'[Last] = 1 )
RETURN
    SWITCH (
        TRUE (),
        cur_year_month >= min_first_date
            && cur_year_month < last_date, DATEDIFF ( min_first_date, cur_year_month, DAY ),
        MONTH ( cur_year_month ) = MONTH ( last_date )
            && YEAR ( cur_year_month ) = YEAR ( last_date ), DATEDIFF ( min_first_date, last_date, DAY ),
        BLANK ()
    )

Avg =
VAR cur_a =
    NOT ( ISBLANK ( [A] ) )
VAR cur_b =
    NOT ( ISBLANK ( [B] ) )
VAR cur_c =
    NOT ( ISBLANK ( [C] ) )
RETURN
    SWITCH (
        TRUE (),
        cur_a && cur_b
            && cur_c,
            DIVIDE ( [A] + [B] + [C], 3 ),
        cur_a && cur_b
            && NOT ( cur_c ), DIVIDE ( [A] + [B], 2 ),
        cur_a && cur_c
            && NOT ( cur_b ), DIVIDE ( [A] + [C], 2 ),
        cur_b && cur_c
            && NOT ( cur_a ), DIVIDE ( [B] + [C], 2 ),
        cur_a
            && NOT ( cur_b ) && NOT ( cur_c ), [A],
        cur_b
            && NOT ( cur_a ) && NOT ( cur_c ), [B],
        cur_c
            && NOT ( cur_a ) && NOT ( cur_b ), [C]
    )

Please refer the attacched .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Unknown]

In my example I only put 3 customers, but, in reality, I have thousands of customers.
How can I replicate this for all of my customers?
Please note, that my goal is to have the column "Avg", not a column for each of my customers (that was only to explain how column "avg" should be calculated).
Thank you

[Anonymous · Accepted solution]

Hi @Unknown ,

My dax formula is used to create measure(not used to create calculate column), and add it to table visual to achieve your demands.

If you have thousands of customers, these dax cannot suitable for your model, you need adjust the measure, but for dax to achieve it is more complex, I don't advise you to do so.

I have try my best to provide you with a solution, maybe someone else will have a better suggestion.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.