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.
Hello, I am new on this platform and I need help concerning Power BI!
I have a table named Orders, which indicate, for each order, the user name and the date of the order. On the table Users, I would like to create a calculated column named "frequency of orders" which would calculate the frequency of orders for each user.
for example, user3 has made 2 orders, at 7 days of difference, so the frequency should be equal to 7.
Could you help me for that ?
Thanks a lot,
Solved! Go to Solution.
Thanks you so much for your help!
In fact with your formula for "frequency of orders", the results for my file is always 0... I have created columns "first date" and "last date" and "frequency bis" and now it works well!
Thanks you again!!!
first date = CALCULATE( FIRSTDATE ( Orders[created_at] ); FILTER(Orders ; Orders[user_name] = Users[user_name]) )
last date = CALCULATE( LASTDATE( Orders[created_at] ); FILTER(Orders ; Orders[user_name] = Users[user_name]) )
frequency bis = DIVIDE ( DATEDIFF ( Users[first date]; Users[last date]; DAY ); Users[number of orders] - 1; 0 )
Why does your solution minus 1 from the number of orders?
2 Calculated COLUMNS
number of orders = CALCULATE ( COUNTROWS ( Orders ), FILTER ( Orders, Orders[user_name] = Users[user_name] ) ) frequency of orders = DIVIDE ( DATEDIFF ( FIRSTDATE ( Orders[created_at] ), LASTDATE ( Orders[created_at] ), DAY ), Users[number of orders] - 1, 0 )
and the result
Hope this helps!
Thanks you so much for your help!
In fact with your formula for "frequency of orders", the results for my file is always 0... I have created columns "first date" and "last date" and "frequency bis" and now it works well!
Thanks you again!!!
first date = CALCULATE( FIRSTDATE ( Orders[created_at] ); FILTER(Orders ; Orders[user_name] = Users[user_name]) )
last date = CALCULATE( LASTDATE( Orders[created_at] ); FILTER(Orders ; Orders[user_name] = Users[user_name]) )
frequency bis = DIVIDE ( DATEDIFF ( Users[first date]; Users[last date]; DAY ); Users[number of orders] - 1; 0 )
Covering 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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |