cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BasLuc Frequent Visitor
Frequent Visitor

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

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:

 

UserIDCreation User Date
101-05-15
202-06-15
303-07-15
404-08-15
505-09-15

Orders:

Order DateUserID
02-06-151
03-07-151
04-08-152
05-09-154
02-11-155

 

 

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 creationAverage amount orders
110
25
36
42
53
64




 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

Hi @BasLuc 

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

 

 

 

 

 

6 REPLIES 6
Super User
Super User

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

Hi @BasLuc 

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

 

 

 

 

 

BasLuc Frequent Visitor
Frequent Visitor

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

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.

Super User
Super User

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

@BasLuc 

 

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

BasLuc Frequent Visitor
Frequent Visitor

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

@AlB 

 

Example.png

This is the current result

 

 

Desired would be indeed something like:

 

Month since creationAverage amount orders
110
25
36
42
53
64

 

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!

Super User
Super User

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

Hi,

 

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

Highlighted
Super User
Super User

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

Hi @BasLuc ,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!