topic Re: Summarizing data from 2 different tables with Date in DAX Commands and Tips

Summarizing data from 2 different tables with Date

unknown917 — Sun, 05 Jan 2025 17:21:17 GMT

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!

Re: Summarizing data from 2 different tables with Date

uzuntasgokberk — Mon, 14 Jul 2025 13:25:24 GMT

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.

Re: Summarizing data from 2 different tables with Date

unknown917 — Sun, 05 Jan 2025 20:14:52 GMT

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

Re: Summarizing data from 2 different tables with Date

gmsamborn — Sun, 05 Jan 2025 20:22:35 GMT

Hi unknown917

Take a look at this.

Summarizing data from 2 different tables with Date.pbix

Re: Summarizing data from 2 different tables with Date

gmsamborn — Sun, 05 Jan 2025 22:45:28 GMT

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

Re: Summarizing data from 2 different tables with Date

johnt75 — Mon, 06 Jan 2025 16:17:13 GMT

Create a measure like

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

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

Re: Summarizing data from 2 different tables with Date

Anonymous — Tue, 07 Jan 2025 06:14:23 GMT

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