Forum Discussion

unknown917's avatar
unknown917
Icon for Helper IV rankHelper IV
1 year ago
Solved

Summarizing data from 2 different tables with Date

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:

 

 JANFEBMAR
202310012575
202412550200
20257510050

 

Any help will be greatly appreciated,

 

Thank you!

  • johnt75's avatar
    johnt75
    1 year ago

    Create a measure like

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

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

  • Anonymous's avatar
    Anonymous
    1 year ago

    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

6 Replies

  • uzuntasgokberk's avatar
    uzuntasgokberk
    Icon for Super User rankSuper User
    1 year ago

    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 |

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    Create a measure like

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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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