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 cove...
  • johnt75's avatar
    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