Forum Discussion

romovaro's avatar
romovaro
Icon for Responsive Resident rankResponsive Resident
3 years ago

DIfference between 2 columns in 2 different tables.

HI all

 

I have an issue calculating the difference between 2 columns in 2 different tables. Tried to check different solutions on the web but I am unable to see what's wrong

 

 

Many to Many - Both with Date as connector.

Both tables have the same rows:

 

 

 

 

I can create the table I need and want i need is to do a simple calculation:

 

Actuals+fcst (from Monthly Actuals) table MINUS Plan (From FY23 Plan by Country)

 

 

I have the formula:

Country Plan Gap2 = CALCULATE(SUM('Monthly Actuals'[Country Actuals+Fcst])-CALCULATE(SUM('FY23 Plan by Country'[Country])))
 
But then I get plenty of duplicate dates x month when I shoud have only one row x month. The calculation looks good (-363.88K) but should be only one row.
 

 

thanks

 

 


 jgeddes 

 

 

 

 

Need Help

5 Replies

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    3 years ago

    I very rarely (never) build a connection bewteen two tables with a date column. I would recommend creating a date table and using that acts as a bridge between the two tables. You could use a table expression like...
    dimDate =
    CALENDAR(
    MIN('Monthly Actuals'[Date], 'FY23 Plan by Country'[Date]),
    MAX('Monthly Actuals'[Date], 'FY23 Plan by Country'[Date])
    )
    Then create a one to many relationship with both tables. Once that is completed use the date table column in your visuals.
    Hope this gets you pointed in the right direction.

  • romovaro's avatar
    romovaro
    Icon for Responsive Resident rankResponsive Resident
    3 years ago

    Thanks jgeddes (as always),

     

    Tried creating the Calendar but still facing the same issue when trying to do the calculation. Tables recognized the Celandar table but when using the formula, 

     

    Country Plan Gap2 = CALCULATE(SUM('Monthly Actuals'[Country Actuals+Fcst])-CALCULATE(SUM('FY23 Plan by Country'[Country])))

     

    everything is wrong.

     

    • jgeddes's avatar
      jgeddes
      Icon for Super User rankSuper User
      3 years ago

      I just noticed something in your tables that I overlooked previously. Do you have columns for each country for actuals and actuals + forecast etc and then for each counrty in the plan table?

       

      • romovaro's avatar
        romovaro
        Icon for Responsive Resident rankResponsive Resident
        3 years ago

         

         

        hi Jgeddes

         

        In the plan table I only have the Date column and the plan for each country:

         

        and in the Monthly Actuals table I have the Date column + Actuals x country x COlumn.

         

        The "Country" Actuals + Fcst is using the Actuals until January and then from Feb to Jun is using another table to fill the gap.

         

        Argentina Actuals+Fcst =
        VAR ActualsArg = 'Monthly Actuals'[Argentina Actuals]
        RETURN
            IF (ISBLANK (ActualsArg), 'Weekly Slippage'[FY23_Starts Arg], ActualsArg)