cancel
Showing results for
Search instead for
Did you mean:
Highlighted
BasLuc 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:

 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

1 ACCEPTED SOLUTION

Accepted Solutions 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

## 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

## 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

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

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

## Re: Calculate average orders customer first, second, third etc. month 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! 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. 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!