Syncing the filter for a measure obtained combining two measures from different tables

[avinrosh]

I need a help for following situation. I have to deal will two tables. 1. one has revenue data till date 2. second has forecast for sales from the current date. Hence I get the cumulative revenue from the revenue table till date. I use following measure and get following table.

mes = if(contains(RevenueData,RevenueData[Sale/Purchase],"Sales"),calculate(sum(RevenueData[Revenue]), filter(all(Calendar_Period),Calendar_Period[Date]<= max(Calendar_Period[Date]))),sum(RevenueData[Revenue]))

 The line plot for this table reacts to a filter.

Again I have another measure for forecast sales. The measure and the table for this looks like this.

test3 = CALCULATE(sum(ChargeTable[Probablised]), ChargeTable[Status] in {"Secured", "Pipeline"},CROSSFILTER(ChargeTable[Period],Calendar_Period[Date],Both),filter(all(Calendar_Period), Calendar_Period[Date]>= EOMONTH(TODAY(),-1) && Calendar_Period[Date] <= max(Calendar_Period[Date])))

The line plot for this table reacts to the same table.

I want to create a third measure which is sum of these two measure

test4= [mes]+ [test3]

Here I am finding the issue, I expect the values from August will add the foreast of the month and the revenue till date. e.g. 14,617,437.03 + 2,778,236.57. But I get cumulative values of individual tables.

I was able to get the intended table modifying the first measure as following

Revenue_Cumulative = CALCULATE(sum(RevenueData[Revenue in GBP_1]),filter(all(RevenueData), RevenueData[Sale/Purchase] in {"Sales"} && RevenueData[Month] <= max(Calendar_Period[Date]) ))

and get following table

But the same filter reacts only to the second measure test3 and this does not have any effect on the values of the updated "mes" measure.

How can I get the cumulative vales for the combined tables and the line graph for the combined table should react to the same filter.

[v-danhe-msft]

Hi @avinrosh,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft]

Hi @avinrosh,

Due to I could not reproduce your data structure, I have entered some sample data, you could refer to it:

1.Sample data(I have entered two measures):

Measure1 = CALCULATE(SUM(Table1[value]))

Table 1

Measure2 = CALCULATE(SUM(Table2[value]))

Table 2

2.Create a calendar table, a month column and create relationships.

New Table:

Table = CALENDAR("2018/1/1","2018/12/31")

New column:

Month name = FORMAT('Table'[Date],"MMMM")

3.Create a measure and a Table visual. Add the related field, you can see the correct result.

Measure 3 = var a=[Measure1]

            var b=[Measure 2]

            return a+b

If I misunderstood you, could you please offer me more information about your data structure or share your pbix file if possible?

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/px7ui96sl59bctw/Syncing%20the%20filter%20for%20a%20measure%20obtained%20co...

Regards,

Daniel He

[Anonymous]

Have you thought of appending the two tables in Power Query first? Rename the summable columns as the same. Add another column for the source type - value Actuals and Forecast. Judging from your DAX, you may need further manipulation to get both tables to match.
Then with a single table you can use simple DAX to sum across the months.