Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
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!
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.
Excellent!! Thank you!!
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!
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
82 | |
55 | |
40 | |
20 | |
12 |