Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
Hi,
Please clarify how you arrived at the numbers in the average amount orders column? How did you get 10,5 etc.
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!
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |