cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EdenHutch Helper I
Helper I

SUMX across 2 tables WITH date filter?

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
PR20048119 Memorable Member
Memorable Member

Re: SUMX across 2 tables WITH date filter?

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.

View solution in original post

5 REPLIES 5
PR20048119 Memorable Member
Memorable Member

Re: SUMX across 2 tables WITH date filter?

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
EdenHutch Helper I
Helper I

Re: SUMX across 2 tables WITH date filter?

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:

 

power BI question.JPG

 

Is it possible? Thank you!

 
EdenHutch Helper I
Helper I

Re: SUMX across 2 tables WITH date filter?

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!

Highlighted
PR20048119 Memorable Member
Memorable Member

Re: SUMX across 2 tables WITH date filter?

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.

View solution in original post

EdenHutch Helper I
Helper I

Re: SUMX across 2 tables WITH date filter?

Excellent!! Thank you!!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors