Forum Discussion
Hourly Average
Hello everybody!
I've been trying for a while to calculate the hourly average in USD and in # of transactions, but all of my attempts have failed.
I have a data set that contains Order Number, Creation Date, Creation Hour, SKU and Price. What I need is to calculate the average hour by hour in a given date period so that I could compare it with the current day, for example:
Today from 00 to 10, there have been 15 transactions with a value of $1500. The average of the last three months in the same hour range is 12 transaction with a total value of $1200.
| Updated today 10AM | ||
| USD | #Transactions | |
| TODAY | 1500 | 15 |
| AVG L3M | 1200 | 12 |
My dataset is something like this:
| Order | Creation Date | Creation Hour | SKU | Price |
| 1001 | 1/10/2020 | 9 | 45678 | 4.5 |
| 1001 | 1/10/2020 | 9 | 57689 | 8 |
| 1001 | 1/10/2020 | 9 | 87965 | 3.2 |
| 1002 | 1/10/2020 | 10 | 90987 | 10 |
| 1002 | 1/10/2020 | 10 | 57689 | 8 |
| 1003 | 1/10/2020 | 12 | 90573 | 12.5 |
| 1004 | 1/10/2020 | 12 | 57689 | 8 |
| 1005 | 5/10/2020 | 14 | 45678 | 4.5 |
| 1006 | 8/10/2020 | 5 | 57689 | 8 |
| 1007 | 9/10/2020 | 8 | 87965 | 3.2 |
| 1007 | 9/10/2020 | 10 | 90987 | 10 |
| 1007 | 9/10/2020 | 10 | 57689 | 8 |
| 1008 | 10/11/2020 | 11 | 90573 | 12.5 |
| 1008 | 10/11/2020 | 11 | 57689 | 8 |
In which every order number is a transaction.
I have tried using Averagex with no avail.
Thank you all in advance.
Grevatious are you looking for this, you need a datetime column in the data. the pbix is attached.
2 Replies
Grevatious are you looking for this, you need a datetime column in the data. the pbix is attached.
Hi Grevatious
There are many things unclear. What role does SKU and Order number play? You only talk A proper, complete example based on the data you show ont he table would help
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
