Summarizing data from 2 different tables with Date

[unknown917]

I have 2 source tables, one with historical volume, the other with open or future volume.  Each table has a date column.  In my model I've created a unique date table with a range wide enough to cover both source tables.  I created a relationship to each source table from the "Date" table.

My next dilemna is the need to create a visual that blends both the "Historical" and "Future" volume data by year and month.  My inclination is that I need to create a new column in my "Date" table that will summarize the volume data from both the "historical" and "future" tables.

My expected outcome is something like:

	JAN	FEB	MAR
2023	100	125	75
2024	125	50	200
2025	75	100	50

Any help will be greatly appreciated,

Thank you!

[johnt75 · Accepted solution]

Create a measure like

Combined Value = SUM( Historic[Value] ) + SUM( Future[Value] )

Put this in a matrix with columns from your date table.

[Anonymous · Accepted solution]

Hello,johnt75 ,gmsamborn and uzuntasgokberk ,thanks for your concern about this issue.

Your answers are excellent!
And I would like to share some additional solutions below.

Hi,@unknown917 .I am glad to help you.
I tried the idea you suggested for the DATE table that has all the data, and added a new calculated column that has all the data from both tables, which ensures that the data is displayed normally as one row in the matrix

My test data:

This is the optimized dax code

The model relationship:

This is my test code

AllDailySales = 
IF (
    SELECTCOLUMNS ( RELATEDTABLE ( historical ), "a", [DailySales] ) <> BLANK (),
    SELECTCOLUMNS ( RELATEDTABLE ( historical ), "a", [DailySales] ),
    SELECTCOLUMNS ( RELATEDTABLE ( future ), "b", 'future'[Forecast daily sales] )
)

Suggestion2

I also tested the solution proposed by johnt75, which uses a matrix to present the data, and can achieve similar results.

TestAllMeasure =
SUM ( 'historical'[DailySales] ) + SUM ( 'future'[Forecast daily sales] )

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[johnt75 · Accepted solution]

Create a measure like

Combined Value = SUM( Historic[Value] ) + SUM( Future[Value] )

Put this in a matrix with columns from your date table.

[uzuntasgokberk]

Hello @unknown917 ,

You have Dimdate table and made relationship to other tables with Date column. Perfect. So what ı don't understand is why don't you use year and month column from Dimdate and the values from other tables? Based on your case it will be solved.

Kind Regards,
Gökberk Uzuntaş

📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!

🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok |

[unknown917]

doing that resulted in a 2nd column for the 2025 (future) entry.  For instance, 2023 & 2024 existed in one column for January, 2025 existed in a column adjacent.  See below:

	JAN	JAN
2023	100
2024	125
2025		75

[gmsamborn]

Hi @unknown917 

I didn't see your post when I added mine.

Here is an example that has JAN 2025 as Historical instead of Future.

Summarizing data from 2 different tables with Date 2.pbix

[gmsamborn]

Hi @unknown917 

Take a look at this.

Summarizing data from 2 different tables with Date.pbix