Forum Discussion
Continued ContributorCalculating Sales on Specific Date and Time
Hi everyone, I have a complex problem that comes to my mind, and not sure how to do it. Thanks in advance 😊
So I have a sales table with date&time column and its sales amount.
How can I have a measure, that sum's the number of sales starting from 6am on that day until 3am the next day?
Sales Table
| Transaction Date | Sales Amount |
| 26/05/2022 5:15am | 1500 |
| 26/05/2022 7:45pm | 300 |
| 27/05/2022 2:54am | 500 |
| 27/05/2022 3:00pm | 800 |
| 27/05/2022 5:16pm | 900 |
Expected Result
| Date | Total Sales |
| 25/05/2022 | 1500 |
| 26/05/2022 | 800 |
| 27/05/2022 | 1700 |
ronaldbalza2023 , You need to create a new date column
Switch(True(),
timevalue([Transaction Date]) >= time(6,0,0), datevalue([Transaction Date]) ,
timevalue([Transaction Date]) < time(3,0,0), datevalue([Transaction Date])-1 ,
datevalue([Transaction Date]))
3 Replies
ronaldbalza2023 What if the transaction time between 3am to 6am.
I think select one proper 24 hrs format
ronaldbalza2023Hi Anonymous, it will then be added on to the next day if that makes sense. Thanks for taking the time on this.
- amitchandak
Super User
ronaldbalza2023 , You need to create a new date column
Switch(True(),
timevalue([Transaction Date]) >= time(6,0,0), datevalue([Transaction Date]) ,
timevalue([Transaction Date]) < time(3,0,0), datevalue([Transaction Date])-1 ,
datevalue([Transaction Date]))
