Calculate YTD of monthly values, then convert currency, then calculate monthly values

[Anonymous]

Hi there,

I was searching quite a while now for similar topics, got a bit closer to the solution but am stuck now. I hope you can help!

I have data with several countries in its local currency on a monthly basis and I want to convert the currencies into Euro BUT the conversion needs to be done on YTD-figures and after that the monthly figures/difference shall be calculated. I don't want to convert the monthly values directly into Euro - it's different.

So the steps should be like this:

1. Calculate YTD-figures of the monthly values
2. Convert this YTD values into the currency Euro
3. Build the difference of the YTD figures to get monthly figures in Euro

Here you can find my test file: Test-File 

I have two tables added to the Datamodel:

The connections are like this:

and my measures are defined as follows:

(LC = Local Currency, EUR = Euro, YTD = Year-to-date, PM = prior month, CM = current month)

• Sales_LC:=SUM([Value_LC])
• Sales_LC_YTD:=CALCULATE([Sales_LC];DATESYTD('Calendar'[Date]))
• Sales_LC_PM:=CALCULATE([Sales_LC];PARALLELPERIOD('Calendar'[Date];-1;MONTH))
• Sales_LC_PM_YTD:=CALCULATE([Sales_LC_PM];DATESYTD('Calendar'[Date]))

Intermediate Step: <-- This step was necessary to have the correct FX rate for "Sales_EUR_YTD". Converting Sales_LC_YTD with "RELATED(FX_Rates..." didn't work as it gave me just the monthly values and not YTD in Euro.

• Sales_EUR:=SUMX(data;DIVIDE([Sales_LC];RELATED(FX_Rates[FX_Rate])))
• Sales_EUR_PM:=CALCULATE([Sales_eur];PARALLELPERIOD('Calendar'[Date];-1;MONTH))
• FX_CM:=DIVIDE([Sales_LC];[Sales_EUR]) 
• FX_PM:=DIVIDE([Sales_LC_PM];[Sales_EUR_PM])

Final Step:

• Sales_EUR_YTD:=DIVIDE([Sales_LC_YTD];[FX_CM])
• Sales_EUR_YTD_PM:=DIVIDE([Sales_LC_PM_YTD];[FX_PM])
• Sales_EUR_CM:=[Sales_EUR_YTD]-[Sales_EUR_YTD_PM]

My only problem left now is the Grand Total (besides a bit too complicate steps), which should give the sum of the months calculated by "Sales_EUR_CM". 

Can you tell me how to change the measures to get the sum of all months in "Sales_EUR_CM".

Thank you very very much!!

Best regards

Philipp

[Anonymous]

Anyone?

[Mariusz]

Hi @Anonymous 

Try this.

Measure = 
SUMX(
    VALUES( 'Calendar'[Month] ), -- or 'Calendar'[YearMonth]
    [Sales_EUR_YTD]-[Sales_EUR_YTD_PM]
)

 Also, this is a power query forum so I'm going to move your post into DAX Patterns if that ok?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hey Mariusz,

wow! That's amazing! Thank you very much for your fast answer. It took me some time to get it into the main database but I managed it. 

I think we're almost done. There is only one small step ahead: The row total are fine now, but the column totals are not correct. Can you tell me how to fix this last issue?