FX conversion with YTD amounts

[petar_kisdobran]

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).

Thanks in advance!

My best,

Petar

[petar_kisdobran]

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

[v-chenwuz-msft · Accepted solution]

Hi @petar_kisdobran ,

Maybe you can try this:

Measure =
VAR _s =
    SUMMARIZE( 'Table', 'Table'[Month], 'Table'[Entity] )
VAR _a =
    ADDCOLUMNS(
        _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.