Trouble with Calculated column

[STS_Joshua]

Hi, I have two tables. One is pulling actual sales numbers from our SAP and another is a excel sheet with sales projection numbers here are two simple mock ups:

Customer	Month	Proj
A	9/1/2019	6000
A	10/1/2019	8000
A	11/1/2019	5000
A	12/1/2019	10000
B	9/1/2019	5000
B	10/1/2019	7000
B	11/1/2019	4000
B	12/1/2019	9000

Customer	Month	Actual
A	9/1/2019	5678
A	10/1/2019	6789
A	11/1/2019	7891
A	12/1/2019	0
B	9/1/2019	4321
B	10/1/2019	1234
B	11/1/2019	5647
B	12/1/2019	0

I want to add a calculated column to the first table that will take the actual numbers by customer and date from the second table and place them in the projection table for previous months. This would be done in excel by having a formula for each cell do:

=IF(MONTH(MONTH)<MONTH(TODAY()),SUMIFS(Actual,Customer,Customer,Date,Date),Proj)

Customer	Month	Proj	Final
A	9/1/2019	6000	5678
A	10/1/2019	8000	6789
A	11/1/2019	5000	7891
A	12/1/2019	10000	10000
B	9/1/2019	5000	4321
B	10/1/2019	7000	1234
B	11/1/2019	4000	5647
B	12/1/2019	9000	9000

In BI i'm trying to write a calculated column like Final = IF(MONTH(Month)<MONTH(TODAY()),CALCULATE(SUM(ACTUAL),MONTH(Month)=MONTH(Month)),Proj) but it throws back a "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."

[JasonTX]

Proj is getting evaluated in Calculate. Try putting another parenthesis after Month(Month).

[Anonymous · Accepted solution]

Hi JasonTX ,

First create a key column in both table by concatinating Month and Customer then relate the tables using key column, after that you can get Proj from table1 using RELATED function.

Key = Table1[Month]&Table1[Customer]

Proj = RELATED(Table1[Proj])
Relation:

Output:

Best Regards,
Mail2inba4

If this post helps, then please consider Accept it as the solution to help the other members find

[STS_Joshua]

That's a forum typo.

The actual formula that I have in BI is:

Actuals = IF(MONTH('Proj'[Date]'<MONTH(TODAY()),CALCULATE('SALESII'[S2$$],MONTH('Proj'[Date])=MONTH('SalesII'[Date])),'Proj'[Value])

Where both of these tables are linked by the customer field and 'SALESII'[S2$$] is a measure: SUM('SALESII'[USD Total])