Forum Discussion

viralpatel21's avatar
viralpatel21
Helper II
5 years ago
Solved

multiple values from different table and show previous month value

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: N...
Need Help
Show and Tell
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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's avatar
Fowmy
Super User
5 years ago

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's avatar
viralpatel21
Helper II
5 years ago

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