Power Pivot in Excel. First look. Part 2

This post is continue of the PowerPivot in Excel. First look Part 1.  Today I will get the currency rate for each transaction that is done not in rubles currency. The rates are entered usually monthly (but there can exceptions), and rate for the last day of the month determines the rate for the whole month. For example:

All transactions for the period from Feb 24 till Feb 28 in EURO must be converted using rate 40.1; from Feb 1 till Feb 23 – rate 40.11; from Jan 1 till Jan 31 – rate 40.71, etc.

Let’s calculate correct rates using DAX language that is available in Excel 2010 PowerPivot. Instead of showing the final formula, let us build it step-by-step.

First we must get the minimum rate date for the specified transaction. For this purpose we have to filter rate table on two columns – Date and Currency. We can do it using CALCULATE function:

CALCULATE(
min(Rate[date]);
FILTER(Rate; Rate[Date] >= MainData[Date]);
FILTER(Rate; Rate[Currency] = MainData[Currency])
) 

We create a formula to get minimum rate date where rate date is greater or equal to transaction date and rate currency is equal to transaction currency. After having applied these filters for each transaction we get the minimum rate date for each transaction, as it can be seen below:

Now we need to get rate for the date determined in the calculation. For this purpose we will use CALCULATE operator one more time:

=CALCULATE(
MAX(Rate[Rate]);
FILTER(
Rate;
Rate[Date] = MainData[Calculation1]
);
FILTER(Rate; Rate[Currency] = MainData[Currency])
) 

The formula is quite similar – we just get the rate for the specified date and for the specified currency. The MAX operator is using just as a requirement of CALCULATE operator – as it must return only one value. We can use here MIN and the result will be the same.

To sum up, the final formula not to create additional fields:

=CALCULATE(
max(Rate[Rate]);
FILTER(
Rate;
Rate[Date] = CALCULATE(
min(Rate[date]);
FILTER(Rate; Rate[Date] >= MainData[Date]);
FILTER(Rate; Rate[Currency] = MainData[Валюта])
)
);
FILTER(Rate; Rate[Currency] = MainData[Валюта])
) 

And the final columns in the PowerPivot:

Summa rub is calculated quite simple:

=[Rate]*[Sum]