SUMX across 2 tables WITH date filter?

[Anonymous]

Hello, and thanks in advance for your help!

I am trying to create a measure that adds values row by row across two tables (which I have done) but then also filters the data by MonthYear. (And not by report filters or slicers: those will not work with what I am trying to accomplish), For example:

01/2016REVENUE = SUMX(TABLE1,TABLE1[REVENUE]) + SUMX('TABLE2','TABLE2'[REVENUE]) but only return revenue for 01/2016

I have a date column in each table and a calendar table, with a month+year column, Is this even possible?

Thank you!

[Anonymous]

I figured out a way to do it. It's not pretty, but it works. I would still be very open to a more elegant solution if anyone has one!

I created two measures and then a third to add the two.  😐

Table1 01/16 Revenue = CALCULATE(SUMX(Table1,Table1[Revenue]),'Calendar'[Month + Year] = "2016-01")

Table2 01/16 Revenue = CALCULATE(SUMX('Table2','Table2'[Revenue]),'Calendar'[Month + Year] = "2016-01")

01/2016 Total Revenue = Table1[Table1 01/16 Revenue] + [Table2 01/16 Revenue]

Thanks!

[Anonymous · Accepted solution]

try this

01/2016 Total Revenue=CALCULATE(SUM(Table1[Revenue]),filter(Calendar,'Calendar'[Month + Year] = "2016-01"))

                                           + CALCULATE(SUM(Table2[Revenue]),filter(Calendar,'Calendar'[Month + Year] = "2016-01"))

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

Excellent!! Thank you!!

[Anonymous]

Do u want your measure should get filtered by your slicers?
If yes then it is correct what you have written.
If no then edit the interaction between your slicer and visual or edit measure as below
Sumx(allselected(table1),table1[col])

Thanks & regards,
Pravin Wattamwar

[Anonymous]

Hello, thanks for the response! I am not sure I explained myself adequately in my question. I want to update my measure to filter the data to show only a specific time period. I am not sure hot to do this. I do not want to use page/visual filters or slicers, but instead, create a measure that both sums columns across two tables and does so for only specific time periods. Example: I want one measure to sumx Table1[Revenue] + Table2[Revenue] just for 01/2016:

Is it possible? Thank you!