MRR delta using yesterday's rate

[Anonymous]

I have a table of MRR delta (month over month) for each customer, along with its currency.

month             customer_id      mrr_local   mrr_local_delta     currency

2022-03-01        AB01                    100                 null              GBP

2022-04-01        AB01                     90                  -10               GBP

2022-03-01        BC02                     50                  null              EUR

2022-04-01        BC02                    70                    20               EUR

2022-03-01        CD03                    10                   null              GBP

2022-04-01        CD03                    70                    50               GBP

2022-03-01        DE04                    15                   null               AUD

2022-04-01        DE04                    65                    50                AUD

I would like to create a measure that sums up the total mrr_local_delta every month.

In this case, sum mrr_local_delta (in EUR) in April 2022 should be ((-10+50 GBP) /0.84) EUR +   20 EUR + (50/1.52) EUR

For simplification, exchange rate base euro table only contains yesterday's rate.

I tried: 

MRR_EUR_delta =

SUMX ( 'MRR Development', 'MRR Development'[mrr_local_delta]/

LOOKUPVALUE(exchange_rate_base_euro[rate],

exchange_rate_base_euro[currency],'MRR Development'[currency]))

but it seems that the result is off.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please refer the measures.

Measure = 
var _rate = CALCULATE(MAX(exchange_rate_base_euro[rate]),FILTER(exchange_rate_base_euro,exchange_rate_base_euro[currency]=SELECTEDVALUE('MRR Development'[currency])))
var _sum = CALCULATE(SUM('MRR Development'[mrr_local_delta]),ALLEXCEPT('MRR Development','MRR Development'[month],'MRR Development'[currency]))/_rate
var _count = CALCULATE(COUNT('MRR Development'[currency]),ALLEXCEPT('MRR Development','MRR Development'[month],'MRR Development'[currency]))
return
_sum/_count

Measure 2 = SUMX(FILTER(ALLSELECTED('MRR Development'),'MRR Development'[month]=SELECTEDVALUE('MRR Development'[month])),[Measure])

Best Regards,

Jay