Forum Discussion

mrmars's avatar
mrmars
New Member
8 years ago
Solved

[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...
  • Phil_Seamark's avatar
    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 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])