Calculate YTD values with different exchange rates per month

[Anonymous]

Hi,

I would like to calculate the ytd value for the following tables under consideration of the different monthly Exchange rates:

Turnover

Year month key	Value
202201	1000
202202	2000

Exchange rates 

Year month key	Rate
202201	1.1
202202	1.2

Reult shoud be: 
1.1 * 1000 + 1.2 * 2.000

Is there a simple DAX solution which solves my problem with a measure? 

Thanks for your support! 

BR

[ibarrau]

Hi lukmtb08. Considering you have a relationship between those tables and assuming the year month is unique column key for exchange rates, you can build something like this:

Sum value new rates =
SUMX(
    Turnover,
    Turnover[Value] * RELATED('Exchange rates'[keyRate])
)

Once you have the sumarization of the rates and values you can calculate YTD

YTD new rates = 
CALCULATE(
    [Sum value new rates]
    , DATESYTD( CalendarTable[Date] )
)

That should make it work. If you have a different model than my assumption please show us more to help you.

I hope that helps,

[Anonymous]

Dear ibarrau, thank you very much! It works fine. 

One additional question 🙂 

We do not have date fields as we report just monthly data. Therefore we have Reporting months (1-12). 
Can we calculate the YTD value based on a slicer for the Reporting months.

Example.

Slicer = Month 10 

-> YTD measure should calculate the values for 1-10

Is possibile to add a calculate expression? 

Example: 

Sum value new rates YTD =
Calculate(
SUMX(
Turnover,
Turnover[Value] * RELATED('Exchange rates'[keyRate])
),  Table[Month] <= Selectedvalue Table[Month])
)

Thanks you for your support! 🙂

[ibarrau]

Hi. You still can use YTD if you make changes on your model. Create a calendar table to use YTD. Then add a new column for the table with monthly value. Convert the period format 2022-05 to the first day of the month like 2022-05-01. Once you have that date you can relate it with the calendar like any date to use time intelligence measures.

I hope that make sense.