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