Forum Discussion

STS_Joshua's avatar
STS_Joshua
Helper II
6 years ago
Solved

Trouble with Calculated column

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:

 

CustomerMonthProj
A9/1/20196000
A10/1/20198000
A11/1/20195000
A12/1/201910000
B9/1/20195000
B10/1/20197000
B11/1/20194000
B12/1/20199000

 

CustomerMonthActual
A9/1/20195678
A10/1/20196789
A11/1/20197891
A12/1/20190
B9/1/20194321
B10/1/20191234
B11/1/20195647
B12/1/20190

 

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)

 

CustomerMonthProjFinal
A9/1/201960005678
A10/1/201980006789
A11/1/201950007891
A12/1/20191000010000
B9/1/201950004321
B10/1/201970001234
B11/1/201940005647
B12/1/201990009000

 

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."

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

3 Replies

  • JasonTX's avatar
    JasonTX
    Resolver I
    6 years ago

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

    • STS_Joshua's avatar
      STS_Joshua
      Helper II
      6 years ago

      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])

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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