cancel
Showing results for
Did you mean:
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
Community Champion

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
Community Champion

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
Community Champion

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.

Helper I

I use the date columns as the field.

Community Champion

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.

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.

Community Champion

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.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors