Calculate sum by Dates in New Table

[nawillia711]

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?

[az38 · Accepted solution]

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]))

[Mariusz]

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

[nawillia711]

I use the date columns as the field.

[Mariusz]

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

[nawillia711]

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. 

[Mariusz]

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