Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Sum Totals from Two Tables

Hey Everyone

 

First time posting here.

 

I'm struggling to find a way to sum the totals from two tables that have a 1:* relationship but can't seem to get it to display correctly. I need to combine totals for invoices as well as sales orders for a month. The date fields are unrelated as well as one of them is an invoice date and the other we call a ship date. The only commonality is that I need to sum them together for October or November and on and on.

 

How can I get the sum of these two tables by month?

 

 

 

 

Need Help
Show and Tell

4 Replies

  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Hi Anonymous ,

     

    the two tables must be filtered using a common date table.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      That's where my disconnect is unfortunately. It's two dates meaning different things with one being when the sales order comes in and the other when we've completed the order and invoiced the customer.

       

      Would it help to create another date table and then create a relationship using the unique dates from the other tables in order to correlate them?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    You may create a new table for all Year and Month and then sum the two values based on the same Year and Month :

    New Table = DISTINCT( SELECTCOLUMNS( CALENDAR(MIN('Table2'[ship date]),MAX('Table2'[ship date])),"Year",YEAR([Date]),"Month",FORMAT([Date],"mmmm")))

    Sum = 
var _1= CALCULATE(SUM(Table1[open]),FILTER('Table1', YEAR([invoice date])=EARLIER('New Table'[Year]) && [invoice date].[Month]=EARLIER('New Table'[Month])))
var _2= CALCULATE(SUM('Table2'[ship]),FILTER('Table2', YEAR('Table2'[ship date])=EARLIER('New Table'[Year]) && [ship date].[Month]=EARLIER('New Table'[Month])))
return _1 +_2

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.