Forum Discussion
mrmars
8 years agoNew Member
[newbie] Calculations between two tables
Hi everyone, I'm new in Power BI (and databases in general). I'd like to ask your help for a blocker I cannot overtake. I have two tables, the first contains planned hours/revenues for a number o...
- 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])
mrmars
8 years agoNew Member
Phil_Seamark and Ashish_Mathur: thanks, thanks and thanks again.
I actually started with Phil solution, and it worked like a charm!
Ashish_Mathur
8 years agoSuper User
You are welcome.