cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
EdenHutch Frequent Visitor
Frequent Visitor

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
PR20048119 New Contributor
New Contributor

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 New Contributor
New Contributor

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 Frequent Visitor
Frequent Visitor

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 Frequent Visitor
Frequent Visitor

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!

PR20048119 New Contributor
New Contributor

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 Frequent Visitor
Frequent Visitor

Re: SUMX across 2 tables WITH date filter?

Excellent!! Thank you!!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors