cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Felipe_Nery
New Member

Adding Values Between Hours

Hello, I have data with the date/time that a sale occurred, and I need to make a table that gathers sales for all days of a certain time interval.
For example:
10/10/2020 09:15 = $50
11/10/2020 09:40 = $150
12/10/2020 09:50 = $50
10/13/2020 10:09 = $350

In the break:
- 9:00 am to 10:00 am sold in total = $250
- 10:00 am to 11:00 am sold in total = $350

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Felipe_Nery , You can create a time table with buckets and use that. First split date and time from datetime in power query or DAX.

 

In DAX

 

Date = [datetime].date
or
Date = date(year([datetime]),month([datetime]),day([datetime]))

DateTime.Date([datetime])

Time = [datetime].Time
or
Time = Time(hour([datetime]),minute([datetime]),second([datetime]))

 

Create a time table join with time and use that

https://kohera.be/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

https://radacad.com/script-for-creating-time-table-in-power-bi-with-hours-minutes-and-seconds-bucket...

 

Add hourly bucket  like 

 

time([Hour],0,0) & " - " time([Hour]+1,0,0) 

 

sort this on hour if needed

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Felipe_Nery , You can create a time table with buckets and use that. First split date and time from datetime in power query or DAX.

 

In DAX

 

Date = [datetime].date
or
Date = date(year([datetime]),month([datetime]),day([datetime]))

DateTime.Date([datetime])

Time = [datetime].Time
or
Time = Time(hour([datetime]),minute([datetime]),second([datetime]))

 

Create a time table join with time and use that

https://kohera.be/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

https://radacad.com/script-for-creating-time-table-in-power-bi-with-hours-minutes-and-seconds-bucket...

 

Add hourly bucket  like 

 

time([Hour],0,0) & " - " time([Hour]+1,0,0) 

 

sort this on hour if needed

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Thank you, I spent days trying. I am Brazilian, and here I didn't find any tutorial about it.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!