cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## FX conversion with YTD amounts

Hi all,

I was searching similar posts but could not solve the challenge and I'm hoping you could help.

If a table consists of data for few entities, monthly amounts in local currency and monthly fx rates, how can I get YTD amounts divided by current month fx rate and total sum of all entity figures in converted amounts (have tried with measure totalytd, individual lines are calculated correctly but totals are not ok).

For instance:

YTD April entity A would be sum of c3:c6 (190) divided by entity a fx rate for april (1.05) = \$181.

YTD May entity B would be sum of c8:c12 (17,160) divided by entity b fx rate for May (120) = \$143.

I could move fx rates to a separate table if needed (entity and monthly basis).

My best,

Petar

1 ACCEPTED SOLUTION
Community Support

Maybe you can try this:

``````Measure =
VAR _s =
SUMMARIZE( 'Table', 'Table'[Month], 'Table'[Entity] )
VAR _a =
_s,
"sum",
DIVIDE(
CALCULATE(
SUM( 'Table'[Amount (local currency)] ),
FILTER(
ALL( 'Table' ),
[Entity] = EARLIER( 'Table'[Entity] )
&& [Month] <= EARLIER( 'Table'[Month] )
)
),
CALCULATE(
MAX( 'Table'[fx rate] ),
FILTER(
ALL( 'Table' ),
[Entity] = EARLIER( 'Table'[Entity] )
&& [Month] = EARLIER( 'Table'[Month] )
)
)
)
)
RETURN
SUMX( _a, [sum] )
``````

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Frequent Visitor

Thanks, this is fantastic and it works!

As in original source I have few additional columns (dimensions), would it be too much to ask is it possible to add few columns to your solution (eg account # (like 1000,1001, 1002), account name (cash, receivables, payables) or similar)?

Thanks again!

Best regards,

Petar

Community Support

Maybe you can try this:

``````Measure =
VAR _s =
SUMMARIZE( 'Table', 'Table'[Month], 'Table'[Entity] )
VAR _a =
_s,
"sum",
DIVIDE(
CALCULATE(
SUM( 'Table'[Amount (local currency)] ),
FILTER(
ALL( 'Table' ),
[Entity] = EARLIER( 'Table'[Entity] )
&& [Month] <= EARLIER( 'Table'[Month] )
)
),
CALCULATE(
MAX( 'Table'[fx rate] ),
FILTER(
ALL( 'Table' ),
[Entity] = EARLIER( 'Table'[Entity] )
&& [Month] = EARLIER( 'Table'[Month] )
)
)
)
)
RETURN
SUMX( _a, [sum] )
``````

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors