Forum Discussion

petar_kisdobran's avatar
petar_kisdobran
Frequent Visitor
3 years ago
Solved

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

 

Thanks in advance!

 

My best,

Petar

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    3 years ago

    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.

2 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    3 years ago

    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.

  • petar_kisdobran's avatar
    petar_kisdobran
    Frequent Visitor
    3 years ago

    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