cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Calculate sum by Dates in New Table

I have two tables. One has many values for each day. The other has one value for each day. I am trying to sum the values from each day in the first table to the second table. For example:

If table 1 looked like this:

12/31/2019 4:00 AM1
12/31/2019 8:37 PM0.89
1/1/2020 5:00 PM0.99
1/3/2020 6:07 AM12
1/3/2020 8:00 PM3

 

Then table 2 would look like this:

12/31/20191.89
1/1/20200.99
1/2/20200
1/3/202015

 

I have connected the tables with a one to many relationship and have used this formula before, with other sources to get this result: Calculate(Sum('Table 1'[Column 1])) but am unable to get it to work on this particular data source. I have tried as many relationships as I can, just can't seem to get this one to work. Is there any other way to get this to work?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Hi @nawillia711 

I see 2 option

 

1. easy:

drop relationships, create new table

NewTable = UNION(Table1, Table2)

then summarize in visual

 

2. more classical

create a calendar table

CalendarTable = CALENDARAUTO()

add a column into Table1

OnlyDate = DATEVALUE(Table1[Date])

add relationships between CalendarTable and Table1 by new OnlyDate field and between CalendarTable and Table2 by field Date

then add final column into CalendarTable

Column = CALCULATE(SUM(Table1[Value])+SUM(Table2[Value]))

  


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

6 REPLIES 6
Highlighted
Super User III
Super User III

Hi @nawillia711 

 

What fields do you use for your relationship

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Highlighted

I use the date columns as the field.

Highlighted

Hi @nawillia711 

 

Do both tables contain date or is one of them datetime?

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn


 

Highlighted

I have tried both of them being date/time and the one I am going to being date and the one I'm coming from being date/time. 

Highlighted

Hi @nawillia711 

 

Add an extra column in the table with date-time (duplicate of Date-Time column ) and format it to date, later use it to build a relationship.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn


 

Highlighted
Super User IV
Super User IV

Hi @nawillia711 

I see 2 option

 

1. easy:

drop relationships, create new table

NewTable = UNION(Table1, Table2)

then summarize in visual

 

2. more classical

create a calendar table

CalendarTable = CALENDARAUTO()

add a column into Table1

OnlyDate = DATEVALUE(Table1[Date])

add relationships between CalendarTable and Table1 by new OnlyDate field and between CalendarTable and Table2 by field Date

then add final column into CalendarTable

Column = CALCULATE(SUM(Table1[Value])+SUM(Table2[Value]))

  


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors