YTD amount multiplied with latest rate & multiple currencies

[Manuchar]

Hello,

I can use some help on how to face the following issue:

I need to calculate the total sales YTD towards USD when there are sales in multiple currencies.
So what should happen is: total amount local currency * latest rate = YTD USD value.
I have a small test setup with 3 tables: Sales - rates - dates

Can anyone help me with my issue?

Thanks you

Best regards,

Thomas

[v-shex-msft]

Hi @Manuchar,

You can try to use following measure to get total YTD sales based on selected date.

Selected YTD = 
VAR _currency =
    VALUES ( 'Currency'[Currency] )
VAR _selected =
    MAXX ( ALLSELECTED ( DateTable[Date] ), [Date] )
VAR temp =
    GROUPBY (
        SUMMARIZE (
            ALL( 'Sales Records' ),
            [Currency],
            [Amount],
            "Rate",
            VAR _lastdate =
                CALCULATE (
                    MAX ( 'Currency'[Closing Date] ),
                    FILTER (
                        ALL ( 'Currency' ),
                        Currency[Currency] <= EARLIER ( 'Sales Records'[Currency] )
                            && [Closing Date] = _selected
                    )
                )
            RETURN
                LOOKUPVALUE (
                    'Currency'[Rate],
                    'Currency'[Currency], 'Sales Records'[Currency],
                    'Currency'[Closing Date], _lastdate
                )
        ),
        [Currency],
        "Total", SUMX ( CURRENTGROUP (), [Rate] * [Amount] )
    )
RETURN
    SUMX ( FILTER ( temp, [Currency] IN _currency ), [Total] )

Regards,

Xiaoxin Sheng

[AlB]

Hi  @Manuchar

How about 

1. Place  'Date'[Date] in the rows of a matrix visual

2. Place this measure in values of your matrix:

Measure =
CALCULATE (
    SUMX (
        Sales,
        Sales[Amount]
            * LOOKUPVALUE (
                Rate[Rate],
                Rate[Date], Sales[Date],
                Rate[Currency], Sales[Currency]
            )
    ),
    DATESYTD ( 'Date'[Date] )
)

[Manuchar]

hi @AlB

the measures works but it's not the one I need.
The measure calculates each month against the rate of the month and not the latest rate only.
In this example the difference is not so large so it isn't really noticeble but with greater numbers this will increase inmediatly.

This is the difference between your formula and the one I need:

best regards,

Thomas

[AlB]

@Manuchar

I see. Try this then. If it doesn't work please post the sample data in text format so that it can be readily copied. Then I can run a quick test before answering again.

Measure =
CALCULATE (
    SUMX (
        Sales,
        Sales[Amount]
            * LASTNONBLANK (
                CALCULATETABLE ( VALUES ( Rate[Rate] ), Rate[Currency] = Sales[Currency] ),
                1
            )
    ),
    DATESYTD ( 'Date'[Date] )
)

[Manuchar]

@AlB

I got the following error when trying that measure.

Hereby you can find my sample data I used.

Date
1/03/2018
2/03/2018
3/03/2018
4/03/2018
5/03/2018
6/03/2018
7/03/2018
8/03/2018
9/03/2018
10/03/2018
11/03/2018
12/03/2018
13/03/2018
14/03/2018
15/03/2018
16/03/2018
17/03/2018
18/03/2018
19/03/2018
20/03/2018
21/03/2018
22/03/2018
23/03/2018
24/03/2018
25/03/2018
26/03/2018
27/03/2018
28/03/2018
29/03/2018
30/03/2018
31/03/2018
1/04/2018
2/04/2018
3/04/2018
4/04/2018
5/04/2018
6/04/2018
7/04/2018
8/04/2018
9/04/2018
10/04/2018
11/04/2018
12/04/2018
13/04/2018
14/04/2018
15/04/2018
16/04/2018
17/04/2018
18/04/2018
19/04/2018
20/04/2018
21/04/2018
22/04/2018
23/04/2018
24/04/2018
25/04/2018
26/04/2018
27/04/2018
28/04/2018
29/04/2018
30/04/2018

Closing Date,Currency,Rate
31/03/2018,USD,1
31/03/2018,EUR,1.2
31/03/2018,ZAR,4
31/03/2018,AED,1.15
30/04/2018,USD,1
30/04/2018,EUR,1.3
30/04/2018,ZAR,3.75
30/04/2018,AED,1.1

Closing date,Amount,Currency
31/03/2018,100,USD
31/03/2018,500,EUR
31/03/2018,150,ZAR
31/03/2018,200,AED
30/04/2018,400,USD
30/04/2018,215,EUR
30/04/2018,1000,ZAR

Best regards,

Thomas

[AlB · Accepted solution]

@Manuchar

Let's try this then

Total Sales USD =
CALCULATE (
    SUMX (
        Sales;
        Sales[Amount]
            * CALCULATE (
                VALUES ( Rate[Rate] );
                TOPN (
                    1;
                    CALCULATETABLE ( Rate; FILTER ( Rate; Rate[Currency] = Sales[Currency] ) );
                    Rate[Closing Date]; DESC
                )
            )
    );
    DATESYTD ( 'Date'[Date] )
)

[Manuchar]

@AlB

Thank you so much, this is what I was looking for.

Br

Thomas

[AlB]

@Manuchar

Cool. Glad it helped.