Skip to main content
The way to achieve your own success is the willingness to help somebody else. Go for it!
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Calculating and explaining cost difference - FX rate and base pay increase


I've got a table with following columns:

ID ReportPeriod Region Country Compensation LCY Compensation USD
1 2022-01 APAC China 1000 153.86
1 2022-02 APAC China 1010 155.40
2 2022-01 APAC Japan 257884 2114.64
2 2022-02 APAC Japan 257884 2000
3 2022-01 APAC China 1000 153.86
3 2022-02 APAC China 1000 154.60
4 2022-01 APAC Japan 257884 2114.64
4 2022-02 APAC Japan 257884 2000
5 2022-01 APAC China 1000 153.86
5 2022-02 APAC China 1000 154.60
6 2022-01 APAC Japan 257884 2114.64
6 2022-02 APAC Japan 300000 2326.63


I want to be able to show in a pivot table differences in cost and reason for these. 

I calculate month to month difference using below set analysis and it works fine:

[LCY Difference]:

Sum([Compensation LCY])-
sum({<ReportPeriod={"$(=date(addmonths(date#(only(ReportPeriod),'YYYY-MM'),-1),'YYYY-MM'))"}>}[Compensation LCY])

[USD Difference]:

Sum([Compensation USD])-
sum({<ReportPeriod={"$(=date(addmonths(date#(only(ReportPeriod),'YYYY-MM'),-1),'YYYY-MM'))"}>}[Compensation USD])

But I would also like to be able to show which changes are related to Base Pay Increase and which FX Rate Adjustment.

FX Rate adjustment happens when there is no difference in local currency, but in USD only, and my expression looks like this:

if([LCY Difference]=0, ((Sum([Compensation USD])-sum({<ReportPeriod={"$(=date(addmonths(date#(only(ReportPeriod),'YYYY-MM'),-1),'YYYY-MM'))"}>}[Compensation USD]))))

Base Pay Increase happens when there is difference in local currency, so I assumed below expression:

if([LCY Difference]>0, ((Sum([Compensation USD])-sum({<ReportPeriod={"$(=date(addmonths(date#(only(ReportPeriod),'YYYY-MM'),-1),'YYYY-MM'))"}>}[Compensation USD]))))

Unfortunately neither of those gives me correct results... I was wondering if anyone has any suggestions how to correctly write those.

Thank you!

Labels (3)
1 Reply
Partner - Contributor III
Partner - Contributor III

Check if it works for you.

Table: Pivot

Fields in script: ID,
Date( Date#(ReportPeriod,'YYYY-MM'),'YYYY-MM') as Report_Date,
"Compensation LCY" as LCY,
"Compensation USD" as USD

Master measures: 1. Change in LCY = Sum(LCY)-above(Column(1))

2. Change in USD = Sum(USD)-above(Column(2))

Other measures: 3. FX Rate = if([Change in LCY]=0,[Change in USD])

4. Base Pay Increase = if([Change in LCY]>0,[Change in USD])