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

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