Forum Discussion
[newbie] Calculations between two tables
- 8 years ago
HI mrmars
I reckon I am close with the following.
1. Create a new calculated table using the following code. I have attached the model in a PBIX file. I had to change your [Month] columns to actual Date datatypes to get the cumulative calculation going.
New Table = VAR BaseTalble = SUMMARIZECOLUMNS( 'planned'[Name], 'planned'[Month], "Planned Hours",SUM('planned'[Planned Hours]), "Planned Revenue",SUM('planned'[Planned Revenue]) ) VAR AddActual = ADDCOLUMNS( BaseTalble, "Actual Hours",SUMX(FILTER('actual','actual'[Name] = EARLIER('planned'[Name]) && 'actual'[Month] = EARLIER('planned'[Month])),'actual'[Actual Hours]) , "Actual Revenue",SUMX(FILTER('actual','actual'[Name] = EARLIER('planned'[Name]) && 'actual'[Month] = EARLIER('planned'[Month])),'actual'[Actual Revenue]) ) RETURN AddActualThen add the following two calculated columns to the new calculated table/
Running Actual Revenue = SUMX( FILTER( 'New Table', 'New Table'[Name] = EARLIER('New Table'[Name]) && 'New Table'[Month] <= EARLIER('New Table'[Month]) ), 'New Table'[Actual Revenue])Running Planned Revenue = SUMX( FILTER( 'New Table', 'New Table'[Name] = EARLIER('New Table'[Name]) && 'New Table'[Month] <= EARLIER('New Table'[Month]) ), 'New Table'[Planned Revenue])
HI mrmars
I reckon I am close with the following.
1. Create a new calculated table using the following code. I have attached the model in a PBIX file. I had to change your [Month] columns to actual Date datatypes to get the cumulative calculation going.
New Table =
VAR BaseTalble =
SUMMARIZECOLUMNS(
'planned'[Name],
'planned'[Month],
"Planned Hours",SUM('planned'[Planned Hours]),
"Planned Revenue",SUM('planned'[Planned Revenue])
)
VAR AddActual =
ADDCOLUMNS(
BaseTalble,
"Actual Hours",SUMX(FILTER('actual','actual'[Name] = EARLIER('planned'[Name]) && 'actual'[Month] = EARLIER('planned'[Month])),'actual'[Actual Hours]) ,
"Actual Revenue",SUMX(FILTER('actual','actual'[Name] = EARLIER('planned'[Name]) && 'actual'[Month] = EARLIER('planned'[Month])),'actual'[Actual Revenue])
)
RETURN AddActual
Then add the following two calculated columns to the new calculated table/
Running Actual Revenue =
SUMX(
FILTER(
'New Table',
'New Table'[Name] = EARLIER('New Table'[Name]) &&
'New Table'[Month] <= EARLIER('New Table'[Month])
),
'New Table'[Actual Revenue])Running Planned Revenue =
SUMX(
FILTER(
'New Table',
'New Table'[Name] = EARLIER('New Table'[Name]) &&
'New Table'[Month] <= EARLIER('New Table'[Month])
),
'New Table'[Planned Revenue])
Phil_Seamark and Ashish_Mathur: thanks, thanks and thanks again.
I actually started with Phil solution, and it worked like a charm!
- Ashish_Mathur8 years agoSuper User
You are welcome.