Calculate average orders customer first, second, third etc. month

[Anonymous]

The problem I have is that I want to calculated the amount of orders an user places per month since creation.

I have two tables User:

UserID	Creation User Date
1	01-05-15
2	02-06-15
3	03-07-15
4	04-08-15
5	05-09-15

Orders:

Order Date	UserID
02-06-15	1
03-07-15	1
04-08-15	2
05-09-15	4
02-11-15	5

I wanted to COUNTROWS per user in the offset period since the account creation, but I can only find functions or patterns to do a fix period for ALL users.

Does someone know how to calculate per user the amount of orders in the first, second etc. month of it's lifetime?

Desired outcome would be something like.

Month since creation	Average amount orders
1	10
2	5
3	6
4	2
5	3
6	4

[Ashish_Mathur]

Hi,

Please clarify how you arrived at the numbers in the average amount orders column?  How did you get 10,5 etc.  

[AlB · Accepted solution]

Hi @Anonymous 

Try this:

1. Create a 1-to-many unidirectional relationship between UserTable[UserID] and OrdersTable[UserID]

2. Create a one-column calculated table:

AuxTable = 
VAR _MaxDiff =
    MAXX (
        ADDCOLUMNS (
            UserTable;
            "MonthDiff"; DATEDIFF (
                UserTable[Creation User Date];
                CALCULATE ( MAX ( OrdersTable[Order Date] ) );
                MONTH
            )
        );
        [MonthDiff]
    )
RETURN
    SELECTCOLUMNS ( GENERATESERIES ( 1; _MaxDiff + 1); "MonthSinceCreation"; [Value] )

3. Place AuxTable[MonthSinceCreation] just created in the previous step in the rows of a matrix visual

4. Create this measure and place it in values of the matrix visual:

AverageAmountOrders = 
AVERAGEX (
    ALL ( UserTable);
    CALCULATE (
        COUNT ( OrdersTable[Order Date] );
        FILTER (
            CALCULATETABLE ( OrdersTable );
            DATEDIFF ( UserTable[Creation User Date]; OrdersTable[Order Date]; MONTH )
                <= (SELECTEDVALUE ( AuxTable[MonthSinceCreation] ) - 1)
        )
    )
) + 0

[Anonymous]

Hi @AlB,

Thank you very much for helping out.

When I put these in a matrix I get only a total average. It seems something goes wrong with making the series.

Do you have further suggestions by any chance?

Thank you very much again for helping.

[AlB]

@Anonymous 

what do you mean by a total average? I would need a clear example based on sample data with the expected result to understand what you are trying to do

[Anonymous]

@AlB 

This is the current result

Desired would be indeed something like:

Month since creation	Average amount orders
1	10
2	5
3	6
4	2
5	3
6	4

So the matrix like you suggested but then that you can see the trend that for example more people do purchases the first month and then it dies off or that it increases the first months and then dies off for example.

So maybe something goes wrong with calculating the DATEDIFF.

An extra related made sure the relationship was calculated.
Thank you!

[MFelix]

Hi @Anonymous ,

Believe that the values presented on your matrix visual are not giving you the expected result because like @AlB  said you need to place the column AuxTable[MonthSinceCreation] on the rows of the matrix, this will give you the 1 , 2 ,3, 4 ...

Regards,

Mfelix