Forum Discussion

kip's avatar
kip
Regular Visitor
7 years ago
Solved

Cumulative currency conversion with YTD and Lookupvalue problem

Hi   I would need some help with an issue I'm facing and I don't understand.   In my simplified model I have 3 tables (2 sales tables just to demonstrate the issue); Date; exchange rates and sale...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI kip,

     

    You can try to use below measure to calculate YTD sales:

    Measure =
SUMX (
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'PBI Test Sales' ),
            [Date] <= MAX ( [Date] )
                && YEAR ( [Date] ) = YEAR ( MAX ( [Date] ) )
                && [Version] IN ALLSELECTED ( 'PBI Test Sales'[Version] )
        ),
        [YYMM],
        [Date],
        [Version],
        [Sales],
        "Rate", LOOKUPVALUE (
            'PBI Test rate'[Rate],
            [YYMM], VALUE ( FORMAT ( [Date], "yymm" ) )
        )
    ),
    [Sales] * [Rate]
)

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
7 years ago

HI kip,

 

You can try to use below measure to calculate YTD sales:

Measure =
SUMX (
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'PBI Test Sales' ),
            [Date] <= MAX ( [Date] )
                && YEAR ( [Date] ) = YEAR ( MAX ( [Date] ) )
                && [Version] IN ALLSELECTED ( 'PBI Test Sales'[Version] )
        ),
        [YYMM],
        [Date],
        [Version],
        [Sales],
        "Rate", LOOKUPVALUE (
            'PBI Test rate'[Rate],
            [YYMM], VALUE ( FORMAT ( [Date], "yymm" ) )
        )
    ),
    [Sales] * [Rate]
)

 

Regards,

Xiaoxin Sheng