multiple values from different table and show previous month value

[viralpatel21]

Hello everyone,

I hope you can help me.

I have 2 tables: FX Rates table and Month FX table. I want to multiply each months monthly value with FX rates. But when it comes to the last month i.e: November (Last month of FX Rates table) there is no data so I would like to see the value of the previous month. i.e. October total in Month FX table

FX Rate table

Months	EUR_Avg	GBP_Avg
Jan-20	1.110785	1.307724
Feb-20	1.091858	1.29805
Mar-20	1.106977	1.237516
Apr-20	1.087027	1.240702
May-20	1.092001	1.230224
Jun-20	1.125007	1.25122
Jul-20	1.143897	1.264336
Aug-20	1.182503	1.312971
Sep-20	1.179359	1.296261
Oct-20	1.176869	1.297643
Nov-20

Month fx Table

Months	EUR_Avg	GBP_Avg	Measure SUM fx RATE x EUR	Measure SUM fx RATE x GBP
Jan-20	50	82	55.53925	1.307724
Feb-20	74	522	80.797492	1.29805
Mar-20	89	33	98.520953	1.237516
Apr-20	85	54	92.397295	1.240702
May-20	28	26	30.576028	1.230224
Jun-20	48	56	54.000336	1.25122
Jul-20	465	654	531.912105	1.264336
Aug-20	20	24	23.65006	1.312971
Sep-20	41	26	48.353719	1.296261
Oct-20	22	88	25.891118	1.297643
Nov-20			25.891118	1.297643

Hope this made some sense

[v-alq-msft · Accepted solution]

Hi, @viralpatel21 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

FX Rate:

Month fx:

There is a relationship between two tables based on 'Months' column. You may create two measures as below.

Measure SUM fx RATE x EUR = 
SUMX(
    SUMMARIZE(
        'Month fx',
        'Month fx'[Months],
        "Result",
        var m = MAX('Month fx'[MonthValue])
        var lastmonth = 
        CALCULATE(
            MAX('Month fx'[MonthValue]),
            FILTER(
                ALL('Month fx'),
                [MonthValue]<m
            )
        )
        var eurfx = 
        CALCULATE(
            SUM('FX Rate'[EUR_Avg]),
            FILTER(
                ALL('FX Rate'),
                [MonthValue]=lastmonth
            )
        )
        var eurmfx = 
        CALCULATE(
            SUM('Month fx'[EUR_Avg]),
            FILTER(
                ALL('Month fx'),
                [MonthValue]=lastmonth
            )
        )
        return
        IF(
            ISBLANK(SUM('FX Rate'[EUR_Avg]))||ISBLANK(SUM('Month fx'[EUR_Avg])),
            eurfx*eurmfx,
            SUM('FX Rate'[EUR_Avg])*SUM('Month fx'[EUR_Avg])
        )
    ),
    [Result]
)

Measure SUM fx RATE x GBP = 
SUMX(
    SUMMARIZE(
        'Month fx',
        'Month fx'[Months],
        "Result",
        var m = MAX('Month fx'[MonthValue])
        var lastmonth = 
        CALCULATE(
            MAX('Month fx'[MonthValue]),
            FILTER(
                ALL('Month fx'),
                [MonthValue]<m
            )
        )
        var gbpfx = 
        CALCULATE(
            SUM('FX Rate'[EUR_Avg]),
            FILTER(
                ALL('FX Rate'),
                [MonthValue]=lastmonth
            )
        )
        var gbpmfx = 
        CALCULATE(
            SUM('Month fx'[EUR_Avg]),
            FILTER(
                ALL('Month fx'),
                [MonthValue]=lastmonth
            )
        )
        return
        IF(
            ISBLANK(SUM('FX Rate'[GBP_Avg]))||ISBLANK(SUM('Month fx'[GBP_Avg])),
            gbpfx*gbpmfx,
            SUM('FX Rate'[GBP_Avg])*SUM('Month fx'[GBP_Avg])
        )
    ),
    [Result]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@viralpatel21 

Are you doing a merge tables option to get the values?

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[viralpatel21]

Hello, 

So i have a calender table in my report, and I have used the USERRELATIONSHIP dax for 2 tables. I was able to figure it out however I want it amend the question:

So I wanted the November valuie to multiply by October FX rate (as November Fx rate is not available yet. 

This is the current DAX that i created where it mirrors Octobers value.

2EURConversion =
var convertusd = CALCULATE(
SUM('FX Rates'[EUR_Avg]) * [Card Spend EUR],
USERELATIONSHIP(Presentment[Settlement Date],FeeDate[Date]),
USERELATIONSHIP(FeeDate[Months],'FX Rates'[Months])
)
var previousmth = CALCULATE(
SUM('FX Rates'[EUR_Avg]) * [Card Spend EUR],
PREVIOUSMONTH(FeeDate[Date]),
USERELATIONSHIP(Presentment[Settlement Date],FeeDate[Date]),
USERELATIONSHIP(FeeDate[Months],'FX Rates'[Months])
)
return
IF(ISBLANK(convertusd),previousmth,convertusd)

Hope this make sense