locked
Creating a Calculated Table to sum values within each month for multiple tables. RRS feed

  • Question

  • I've got a table that list the budget and another that lists the transactions. Each of these tables has accounts where the budget and transactions tie to. I'd like to be able to create a table that contains both values. So have a column that lists each month and then a column for each total budget and total of transactions. 
    Tuesday, July 3, 2018 6:40 PM

All replies

  • Hi jshinnenkamp,

    Thanks for your question.

    >>>>I'd like to be able to create a table that contains both values. So have a column that lists each month and then a column for each total budget and total of transactions.
    Assuming you have created relationships for these tables correctly, you can then try below sample DAX formula:

    YearMonthBudgetSale = 
                  SUMMARIZE('Date','Date'[YearMonth],
                   "Budget", CALCULATE(SUM(Budget[BudgetAmount])),
                   "Sale",CALCULATE(SUM(Sales[SalesAmount]))
                   )

    See below tested sample data:

     Table Budget:

    Sales Table:

    Relationships:

    Tested results:


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 4, 2018 7:20 AM
  • But in most scenarios you should not need to create a calculated table for this. If you have a separate date table linked to both your Sales and Budget tables (as per Willson's diagram) you can create tables and charts viewing both measures side by side using any of the columns from the date table.

    http://darren.gosbell.com - please mark correct answers

    Wednesday, July 4, 2018 10:43 PM