cancel
Showing results for
Did you mean:
Highlighted
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 AM 1 12/31/2019 8:37 PM 0.89 1/1/2020 5:00 PM 0.99 1/3/2020 6:07 AM 12 1/3/2020 8:00 PM 3

Then table 2 would look like this:

 12/31/2019 1.89 1/1/2020 0.99 1/2/2020 0 1/3/2020 15

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

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()``

``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
6 REPLIES 6
Highlighted
Super User III

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.

Highlighted
Helper I

I use the date columns as the field.

Highlighted
Super User III

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.

Highlighted
Helper I

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
Super User III

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.

Highlighted
Super User IV

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()``

``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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors