Forum Discussion

viralpatel21's avatar
viralpatel21
Icon for Helper II rankHelper 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: 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

MonthsEUR_AvgGBP_Avg
Jan-201.1107851.307724
Feb-201.0918581.29805
Mar-201.1069771.237516
Apr-201.0870271.240702
May-201.0920011.230224
Jun-201.1250071.25122
Jul-201.1438971.264336
Aug-201.1825031.312971
Sep-201.1793591.296261
Oct-201.1768691.297643
Nov-20  

 

Month fx Table

MonthsEUR_AvgGBP_AvgMeasure SUM fx RATE x EURMeasure SUM fx RATE x GBP
Jan-20508255.539251.307724
Feb-207452280.7974921.29805
Mar-20893398.5209531.237516
Apr-20855492.3972951.240702
May-20282630.5760281.230224
Jun-20485654.0003361.25122
Jul-20465654531.9121051.264336
Aug-20202423.650061.312971
Sep-20412648.3537191.296261
Oct-20228825.8911181.297643
Nov-20  25.8911181.297643

 

Hope this made some sense

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.

3 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    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
    Icon for Super User rankSuper 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
      Icon for Helper II rankHelper 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