How to handle the Sales Budget with different currency?

[Aixia]

Hi

I have been struggling to solve the problem ´´Sales Budget with different currency´´. 

I appreciate a lot if I can get any tips and advice how to create the DAX measures for the questions below.

Basic:

1. The company in this case has FY September-August.  
2. SalesBudget (SB) for the coming FYs which needed to divide to the right FYs.  
3. SalesBudget(SB) has different currency which needed to converted to SEK.

Prepared:

PBIX includes 3 tables: SalesBudget, Currency, and Fiscal calender.

Goal:

BS needed to divide to the correct FYs with the currency SEK.

Need help:  

If I understand right, first of all, the SB needed to divide to different FYs, then convent the different currency to SEK. If so,

1. How to calculate the SB and how to divide the SB to correct FYs?
2. How to convent currency from the different FYs to SEK?

Shared files:

https://www.dropbox.com/scl/fi/psqtcie4awagwknu4jh19/Sales-budget-202409-202808.xlsx?rlkey=hukjq33e2...

https://www.dropbox.com/scl/fi/llwq263vkymfpa4egv52o/BudgetSales-with-currency-240326.pbix?rlkey=d64...

Note that the PBIX file need to download first, then can open it.

Regards

Aixia

[Aixia]

Hi @OwenAuger 

I got the result exactly as I wanted! Thanks a lot!

Have a nice day!

Br/Aixia

[Aixia]

Hi

Is there anyone can give me any solutions/suggestions?

Regards

Aixia

[OwenAuger]

Hi there @Aixia 

I had a look and have attached my suggested approach.

1. In Power Query, created a function fnGenerateMonths that generates all "first of the month" dates between two given dates. This is for convenience, to be used in the other queries.
2. Transform DimAnnual Currency with the help fnGenerateMonths (result shown below).
3. Transform BudgetSales also using fnGenerateMonths, and join DimAnnual Currency. Multiply by the rates to produce column Amount SEK.
   (there are a few steps involved so take a look in Power Query, result shown below).
4. In all queries remove unnecessary columns (you can adjust as needed).
5. Don't load DimAnnual Currency to the model (you can load if you need it for another purpose).
6. I turned of auto date/time to tidy up the model and fixed one of the columns in DimFiscal Calendar by removing ".[Date]".
7. For testing purposes, I created two measures Amount and Amount SEK and a field parameter Measure Selection to allow switching between them.

DimAnnual Currency

BudgetSales

Hope that helps! 🙂

Regards

[Aixia]

Hi @OwenAuger 

Thanks again for your solutions!  You´re amazing!

If you have time, I appreciate if you can explain more about the step of Inserted Multiplication for BudgetSales Table. See below..

What I did:

I added the column from the exampel ( from all column), but I got the strange results:

1) I have given many examples so that the new column understand that it is multiplcation ( Amount*SEK). But it didn´t work. 

2) I got the (wrong) results only 100 of 684 rows. So 584 rows were missing.

What can I do in order to get the correct resuLts as yours? Thanks.

Br/Axiia

[OwenAuger]

Hi again @Aixia 

Just checking you're still having trouble?

• I would not use the "Column From Examples" function for a straightforward calculation.
• The "Inserted Multiplication" step in your screenshot looks correct (in itself).

Could you share you current working file if you're still having trouble?

Regards

[Aixia]

Hi @OwenAuger 

Thanks for your reply. I´m a beginner in PBI. I must to say that I´ve leaned a lot from your solution.

To your questions: Yes. problem remains. 

The "Inserted Multiplication" step in the screenshot came from your solution and that´s correct.

Now I know that "Column From Examples" function isn´t the correct way to calculate multiplication in this case. 

I attached the same pbix which came from you. 

https://www.dropbox.com/scl/fi/zujvbfsw8xg2qk8dx5nsp/BudgetSales-with-currency-240326-Owen-edit.pbix... 

I´d like to know which function you used and how to do it gradually about the step ´´ Inserted Multiplication´´. 

Regards

Aixia

[OwenAuger]

Hi again @Aixia 

Here is a quick recording of how I created the "Inserted Multiplication" step using the interface.

You could als use the Add Column > Custom Column dialog box.

Hope that helps! 

Regards,

Owen

[Aixia]

Hi @OwenAuger 

Thanks a lot for explaining! Appreciate a lot!

Have a nice day.

Br/Aixia

[OwenAuger]

Hi @Aixia 

Here's how I created the field parameters for measure selection between Amount Local Currency and Amount SEK:

Regards

[Aixia]

Hi @OwenAuger 

Thanks!!

I appreciate it a lot for your explaination. 

Br/Aixia

[Aixia · Accepted solution]

Thanks @OwenAuger 

I´ll check & let you know then. Thanks again.

Br/Aixia

[v-tianyich-msft]

Hi @Aixia ,

I have a question, if this user belongs to more than one period at the same time, how does this SEK need to be calculated?

Best regards,
Community Support Team_ Scott Chang

[Aixia]

Hi @v-tianyich-msft 

Nice to get some response to this problem. Thanks!

To your question/example:

1) 

Since the budget belong to the different period for every user, we need to divide the budget to the correct fiscal year first (Sep.-Aug.). See below. 

2) 

Then we need to convent currency from different fiscal year to SEK. It will be like this in the end.

Looking forward to any solutions/suggestions. 

Kind regards

Aixia