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

[iczkla]

Hello,

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!

[GeorgePhilips23]

Check if it works for you.

Table: Pivot

Fields in script: ID,
Date( Date#(ReportPeriod,'YYYY-MM'),'YYYY-MM') as Report_Date,
Region,
Country,
"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])