Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ShaneHelp
Regular Visitor

YTD Sales by Month multiplied by FX Rate for that month

I have a summarised sales fact table with details of the Month, Currency, Company, and Sales Amount. Sample FactSales table:

3.a FactSales sample data.png

 

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:

3b. DimRate sample data.png

 

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:

3c. Sample Report.png

 

Sample table relationships:

1. FactSales to DimRate.png

 

Alternative table relationships:

2. FactSales to Dims to DimRate.png

 

Any suggested solutions would be greatly appreciated as I've been working on this for a while now with no success.

2 REPLIES 2
kentyler
Solution Sage
Solution Sage

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 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


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
MonthKeyCompanyKeyCurrencyKeyRateKeySaleAmt
201901AB12USD201901|AB12|USD100
201901AB12USD201901|AB12|USD200
201901AB12AUD201901|AB12|AUD100
201901XY89EUR201901|XY89|EUR100
201902AB12USD201902|AB12|USD100
201902XY89EUR201902|XY89|EUR100
201903AB12USD201903|AB12|USD100
201903XY89GBP201903|XY89|GBP100
201903XY89EUR201903|XY89|EUR100
201904AB12USD201904|AB12|USD100
201904XY89EUR201904|XY89|EUR100

 

 

DimRate
RateKeyMonthKeyCompanyKeyCurrencyKeyFXRate
201901|AB12|USD201901AB12USD1.23
201901|AB12|AUD201901AB12AUD1.11
201901|AB12|EUR201901AB12EUR1.45
201901|AB12|GBP201901AB12GBP0.89
201901|XY89|USD201901XY89USD1.23
201901|XY89|AUD201901XY89AUD1.11
201901|XY89|EUR201901XY89EUR1.45
201901|XY89|GBP201901XY89GBP0.89
201902|AB12|USD201902AB12USD1.25
201902|AB12|AUD201902AB12AUD1.11
201902|AB12|EUR201902AB12EUR1.37
201902|AB12|GBP201902AB12GBP0.89
201902|XY89|USD201902XY89USD1.25
201902|XY89|AUD201902XY89AUD1.11
201902|XY89|EUR201902XY89EUR1.37
201902|XY89|GBP201902XY89GBP0.89
201903|AB12|USD201903AB12USD1.25
201903|AB12|AUD201903AB12AUD1.11
201903|AB12|EUR201903AB12EUR1.59
201903|AB12|GBP201903AB12GBP0.87
201903|XY89|USD201903XY89USD1.25
201903|XY89|AUD201903XY89AUD1.11
201903|XY89|EUR201903XY89EUR1.59
201903|XY89|GBP201903XY89GBP0.87

 

 

Report = Sample of user filtering for USD
MonthKeyCompanyKeySale AmtYTDFXRateYTD x FXRate
201901AB123003001.23369
201901XY89001.230
201902AB121004001.25500
201902XY89001.250
201903AB121005001.25625
201903XY89001.250
201904AB12100600nullnull
201904XY8900nullnull

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors