Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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."
Solved! Go to 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.
Proj is getting evaluated in Calculate. Try putting another parenthesis after Month(Month).
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.
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |