Calculated Dax column

[Anonymous]

Hi

I am trying to create a new calculated column in Table 1 in which based on the Invoice Currency and FY then the value is multiplied by the relevant currency rate within Table 2. 
Table 3 shows the intended output i am trying to achieve.

Can you please advise on the dax measure required for this?

Table 1Table 2

Table 3

[AlB · Accepted solution]

Hi @Anonymous 

Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

Create a new  calculated table, Table3:

Table3 =
ADDCOLUMNS (
    Table1,
    "FValue",
        VAR rate_ =
            LOOKUPVALUE (
                Table2[Curreny Rate],
                Table2[Financial Year], Table1[FY],
                Table2[Currency], Table1[Invoice Currency]
            )
        RETURN
            Table1[Value] * rate_
)

You could also just add a calculated column to your Table1 instead of creating a new table

This can also be done in Power Query.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Many Thanks @AlB !!

Can you also advise on how to add calculated column in Table 1 instead and also how this can be done within Power Query?

[AlB]

@Anonymous 

Calc colum for Table1:

FValue =
VAR rate_ =
    LOOKUPVALUE (
        Table2[Curreny Rate],
        Table2[Financial Year], Table1[FY],
        Table2[Currency], Table1[Invoice Currency]
    )
RETURN
    Table1[Value] * rate_

 For the Power Query solution I'd need you to paste Table1 and Table2 here in text (just copy the table in PBI and paste here) so that I can copy the contents quickly and build the solution

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Thanks for the swift response, see below. 

PC      Curr    FY        Value 

abc	GBP	FY20	810.08
bcd	GBP	FY20	195.82
efg	GBP	FY21	380.69
hij	GBP	FY21	697.78
klm	EUR	FY20	956.07
mno	EUR	FY20	184.03
pqr	EUR	FY21	1785.86
stu	EUR	FY21	1572.71

Curr     FYr    Rate 

GBP	FY20	1
EUR	FY20	1.129805
GBP	FY21	1
EUR	FY21	1.141068

[AlB]

@Anonymous 

You need to create Table2 first. Then place the following M code in a blank query to see the steps. The last step is the important one (#"Added Custom")

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7LCgIxDAXQf8l6CEnHNulWULciuJDShTO+dXz7/9a6cKibXLgHLgkBlk0LFUyG03THC0MplAlJIVYBmnZVKnuLarKuN9u+copaCZ3PutsfSnVeUL7Lx1OXitF89lv21iFJ1u58KZV1gFRnvd7uff0ss2h6y2V+PF9/bMWgMMT4Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PC = _t, Curr = _t, FY = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PC", type text}, {"Curr", type text}, {"FY", type text}, {"Value", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "FValue", each [Value]*Table.SelectRows(Table2, (inner)=>inner[FY]=[FY] and inner[Curr]=[Curr])[Rate]{0}, type number)
in
    #"Added Custom"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers