Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a summarised sales fact table with details of the Month, Currency, Company, and Sales Amount. Sample FactSales table:
I have a second table which gives me the FX (exchange) Rate for all combinations of Month, Currency, and Company. The RateKey can be an integer column, but am showing it as the combination of the PK columns for the sample. Sample DimRate table:
On the report we are looking to show the Sale Month and the Inception To Date (ITD) sales using the FX Rate for that Sale Month i.e. the full ITD values should be summed up to the month and then multiplied by the FX Rate for that month. Example: For March it is the sum of sales from Jan-Mar multiplied by the March FX Rate, and for April it is the sum of sales from Jan-Apr multiplied by the April FX rate.
Getting the ITD values is easy OK. The sample above is YTD, but I am looking at all sales from the start of time to now. Sample logic:
Sales ITD:=CALCULATE(SUM('FactSales'[SalesAmt]),FILTER(ALL('DimMonth'), 'DimMonth'[MonthKey] <= MAX('DimMonth'[MonthKey])))
The issue I'm having is when I try to multiply the summed up ITD value by the FX Rate for the month e.g. ITD(Sales) x FX Rate. I've tried many variations, but none get the desired results. Generally the results are ITD(Sales x FX Rate) or ITD(Sales) x Last(FX Rate where it is not blank) which works for March only (last FX Rate record), but I need the ability to see [ITD(Sales) x FX Rate] in January using the January rate, or [ITD(Sales) x FX Rate] in February using the February rate.
Sample report:
Sample table relationships:
Alternative table relationships:
Any suggested solutions would be greatly appreciated as I've been working on this for a while now with no success.
Once you are inside the CALCULATE, the filter context can change.
People usually get a value for the current row and assign it to a VAR, then it can be used to do something like a multiplication.
Sales ITD:=
VAR Cur_Month_FX = MAX('dimRate'[FXRate])
VAR Sales_Total = CALCULATE(SUM('FactSales'[SalesAmt]),FILTER(ALL('DimMonth'), 'DimMonth'[MonthKey] <= MAX('DimMonth'[MonthKey])))
RETURN Sales_Total * Cur_Month_FX
If you have difficulties paste in copies of your tables from excel and I'll be able to add them to a power bi file and help work out the details.
Every time I answer a question I learn something
I'm a personal Power BI trainer
Help when you know. Ask when you don't!
Thanks for the suggestion, but unfortunately it didn't get the right result. From what I can see there it is getting the MAX FX Rate which in the sample is 1.59. If the report is looking at February the February FX rate should be taken and then multiplied by the Sales Total so the max would need to be linked to the Month and Company somehow.
I've copied in the tables below as for some reason the excel file won't upload.
FactSales | ||||
MonthKey | CompanyKey | CurrencyKey | RateKey | SaleAmt |
201901 | AB12 | USD | 201901|AB12|USD | 100 |
201901 | AB12 | USD | 201901|AB12|USD | 200 |
201901 | AB12 | AUD | 201901|AB12|AUD | 100 |
201901 | XY89 | EUR | 201901|XY89|EUR | 100 |
201902 | AB12 | USD | 201902|AB12|USD | 100 |
201902 | XY89 | EUR | 201902|XY89|EUR | 100 |
201903 | AB12 | USD | 201903|AB12|USD | 100 |
201903 | XY89 | GBP | 201903|XY89|GBP | 100 |
201903 | XY89 | EUR | 201903|XY89|EUR | 100 |
201904 | AB12 | USD | 201904|AB12|USD | 100 |
201904 | XY89 | EUR | 201904|XY89|EUR | 100 |
DimRate | ||||
RateKey | MonthKey | CompanyKey | CurrencyKey | FXRate |
201901|AB12|USD | 201901 | AB12 | USD | 1.23 |
201901|AB12|AUD | 201901 | AB12 | AUD | 1.11 |
201901|AB12|EUR | 201901 | AB12 | EUR | 1.45 |
201901|AB12|GBP | 201901 | AB12 | GBP | 0.89 |
201901|XY89|USD | 201901 | XY89 | USD | 1.23 |
201901|XY89|AUD | 201901 | XY89 | AUD | 1.11 |
201901|XY89|EUR | 201901 | XY89 | EUR | 1.45 |
201901|XY89|GBP | 201901 | XY89 | GBP | 0.89 |
201902|AB12|USD | 201902 | AB12 | USD | 1.25 |
201902|AB12|AUD | 201902 | AB12 | AUD | 1.11 |
201902|AB12|EUR | 201902 | AB12 | EUR | 1.37 |
201902|AB12|GBP | 201902 | AB12 | GBP | 0.89 |
201902|XY89|USD | 201902 | XY89 | USD | 1.25 |
201902|XY89|AUD | 201902 | XY89 | AUD | 1.11 |
201902|XY89|EUR | 201902 | XY89 | EUR | 1.37 |
201902|XY89|GBP | 201902 | XY89 | GBP | 0.89 |
201903|AB12|USD | 201903 | AB12 | USD | 1.25 |
201903|AB12|AUD | 201903 | AB12 | AUD | 1.11 |
201903|AB12|EUR | 201903 | AB12 | EUR | 1.59 |
201903|AB12|GBP | 201903 | AB12 | GBP | 0.87 |
201903|XY89|USD | 201903 | XY89 | USD | 1.25 |
201903|XY89|AUD | 201903 | XY89 | AUD | 1.11 |
201903|XY89|EUR | 201903 | XY89 | EUR | 1.59 |
201903|XY89|GBP | 201903 | XY89 | GBP | 0.87 |
Report = Sample of user filtering for USD | |||||
MonthKey | CompanyKey | Sale Amt | YTD | FXRate | YTD x FXRate |
201901 | AB12 | 300 | 300 | 1.23 | 369 |
201901 | XY89 | 0 | 0 | 1.23 | 0 |
201902 | AB12 | 100 | 400 | 1.25 | 500 |
201902 | XY89 | 0 | 0 | 1.25 | 0 |
201903 | AB12 | 100 | 500 | 1.25 | 625 |
201903 | XY89 | 0 | 0 | 1.25 | 0 |
201904 | AB12 | 100 | 600 | null | null |
201904 | XY89 | 0 | 0 | null | null |
User | Count |
---|---|
42 | |
27 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |