cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors