Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Sum measures with different date selections

Hi,

 

I want to calculate a FX impact on P&L and Balance sheet concepts.

 

The data loaded in my model is different according to the specific concept:

  • Data regarding Debt is absolute (i.e. September: Total debt at Septembe 30th)
  • Other data is variance (i.e. total operating cash flows): Data for September is actually the result of September - August

I cannot change the original data

 

Formula for calculating FX impact on P&L and Balance is: Y - W

when Y: total operating cash flows + total investing cash flows + total financing cash flows + FX impact on debt

when W: initial debt - final debt + Y

FX impact on debt: (Initial debt / FX initial) - (Initial debt / FX final)

 

To calculate this for a Q3 period (September to June)

  •  FX impact on debt requires the selection of final an initial debt (September and June), I manage to calculate this by editing interactions.
  • Total operating cash flows: sum of total operating cash flows per month (September + August + July) - not adding June because my data on June is the variance between June and May.

 

Thus, when trying to sum FX impact on debt + total operating cash flows, it doe not work as each measure is affected by different dates.

 

Any idea of how should I perform this? Any help would be more than welcomed.

 

Thank you very much for your help in advance

 

Regards, 

 

Need Help

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous 

    Do you have data as below?

     

    FX impact on debt: (Initial debt / FX initial) - (Initial debt / FX final)

    What is  FX initial,  FX final?

     

    when trying to sum FX impact on debt + total operating cash flows, it doe not work as each measure is affected by different dates.

    what is the measure?

    Is FX impact on debt  a measure?

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-juanli-msft ,

       

      Thanks for your reply. I will try to explain:

       

      FX initial and FX final are data from a table like this.

             Month      Primary Currency        Secondary Currency             FX

             June         EUR                             USD                                       1,213455

             July          EUR                             USD                                       1,21789

             ...             

      When trying to identify the FX impact on Q3, the Fx initial will be the FX in June and the FX final will be the FX in September.

       

      Regarding this table, the primary currency is always Euros.

      Thus, FX impact on debt measure searchs the specifc FX for the correspondent debt (initial and final) for a company using a relationship with the master table where I have which secondary currency corresponds to each company.

      The objective is to show the FX impact on debt always in Euros (i.e. if the secondary and primary currency is Euros, the imapct is 0).

       

      FX impact on debt, total operating cash flows, total investing cash flows and total financing cash flows are ALL measures.

      That is why adding all this measures affected by different dates does not work.

       

      Thanks for your help in advance!!

       

      Regards,