Forum Discussion

juangomez's avatar
juangomez
Icon for Helper I rankHelper I
6 years ago

Divide value by specific monthly value in another table

Hello

 

I have a table with monthly sales from 2018 to 2020 (Forecast).

I have another table with multiple exchange rates for each month.

- 2 different scenario for 2020 dates & another for 2019 dates.

 

VersionDateto US
FCST 1 20201/01/2020 3,190.3780
FCST 1 20201/02/2020 3,190.3780
FCST 1 20201/03/2020 3,190.3780
FCST 1 20201/04/2020 3,190.3780
FCST 1 20201/05/2020 3,190.3780
FCST 1 20201/06/2020 3,190.3780
FCST 2 20201/01/2020 2,943.6855
FCST 2 20201/02/2020 2,877.5920
FCST 2 20201/03/2020 2,939.0248
FCST 2 20201/04/2020 2,871.8590
FCST 2 20201/05/2020 2,924.0452
20191/01/2019 2,872.4117
20191/02/2019 2,856.5965
20191/03/2019 2,846.0773
20191/04/2019 2,766.8433
20191/05/2019 2,856.7091
20191/06/2019 2,890.7343

 

I now need to divide each monthly sales by the exchange rate of the same month (not the year nor date) to have comparable data.

 

Something like:

2019 US Sales = (January 2019 sales) / [FCST 1 2020] January Exchange rate

2020 US Sales = (January 2020 sales) / [FCST 1 2020] January Exchange rate

 

I really don't know where to start.

 

Thanks everyone for your help.

 

Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Please find formulas

    2019 US Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)),Version<>"FCST 1 2020")
OR
2019 US Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-12,MONTH),Version<>"FCST 1 2020")
//In case you have slicer , you do not need a filter for 2020, else you need to add filter for both 2020 and 2019
2020 US Sales = CALCULATE(SUM(Sales[Sales Amount]),Version="FCST 1 2020")
//You can only have one rate has you are using as measure. Again date filter is only needed when you do not have slicer
FCST 1 2020 January Exchange rate = maxx(Exchange,Exchange[Rate])

     

    You can divide it as per need.

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
    Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

    Connect on Linkedin

    • juangomez's avatar
      juangomez
      Icon for Helper I rankHelper I
      6 years ago

      Hello

       

      Thanks for the quick response.

      I believe my request was not clear enough.

       

      Table 'Sales' has monthly sales 

      DateSales
      ene-19 100.0000
      feb-19 200.0000
      mar-19 300.0000
      abr-19 400.0000
      may-19 500.0000
      jun-19 600.0000
      jul-19 700.0000
      ago-19 800.0000
      sep-19 900.0000
      oct-19 1,000.0000
      nov-19 1,100.0000
      dic-19 1,200.0000
      ene-20 1,300.0000
      feb-20 1,400.0000
      mar-20 1,500.0000
      abr-20 1,600.0000
      may-20 1,700.0000

       

      Previous table has no relationship with Table 'Exchange Rate'.

      VersionDateto US
      FCST 1 20201/01/2020 3,190.3780
      FCST 1 20201/02/2020 3,190.3780
      FCST 1 20201/03/2020 3,190.3780
      FCST 1 20201/04/2020 3,190.3780
      FCST 1 20201/05/2020 3,190.3780
      FCST 1 20201/06/2020 3,190.3780
      FCST 2 20201/01/2020 2,943.6855
      FCST 2 20201/02/2020 2,877.5920
      FCST 2 20201/03/2020 2,939.0248
      FCST 2 20201/04/2020 2,871.8590
      FCST 2 20201/05/2020 2,924.0452
      20191/01/2019 2,872.4117
      20191/02/2019 2,856.5965
      20191/03/2019 2,846.0773
      20191/04/2019 2,766.8433
      20191/05/2019 2,856.7091
      20191/06/2019 2,890.7343

       

      Now, I have to calculate the US Sales:

      For each data point, I need to find the corresponding exchange rate according to the month (independant of the year).

       

      If slicer for the Exchange Rate is selected as: FCST 1 2020 the result should be:

      DateSalesFCST 1 2020US Sales
      ene-19100.003,190.380.03
      feb-19200.003,190.380.06
      mar-19300.003,190.380.09
      abr-19400.003,190.380.13
      may-19500.003,190.380.16
      jun-19600.003,190.380.19
      ene-201,300.003,190.380.41
      feb-201,400.003,190.380.44
      mar-201,500.003,190.380.47
      abr-201,600.003,190.380.50
      may-201,700.003,190.380.53
      jun-201,700.003,190.380.53

       

      But if slicer for the Exchange Rate is selected as: FCST 2 2020 the result should be:

      DateSalesFCST 2 2020US Sales
      ene-19100.002,943.690.03
      feb-19200.002,877.590.07
      mar-19300.002,939.020.10
      abr-19400.002,871.860.14
      may-19500.002,924.050.17
      jun-19600.002,872.410.21
      ene-201,300.002,943.690.44
      feb-201,400.002,877.590.49
      mar-201,500.002,939.020.51
      abr-201,600.002,871.860.56
      may-201,700.002,924.050.58
      jun-201,700.002,872.410.59

       

      In both examples, the exchange rate for Jan 2019 is the same as Jan 2020, Feb 2019 is the same as Feb 2020, Mar 2019 is the same as Mar 2020 and so on. which means I need to match by the month and version, independant of the year the version has.

       

      The result I'm looking for is the US Sales column.

       

      Thanks again for the quick response

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        6 years ago
        Spoiler
        hello juangomez ,

        Please try this:

        USD Sales = 
SUMX (
    Sales,
    DIVIDE (
        //get the equivalent exchange rate based on month name (mmm format) in the sales table
        //must select a version in currency table in order to show the correct rate
        Sales[Sales],
        CALCULATE (
            MAX ( Currency[to US] ),
            FILTER ( Currency, FORMAT ( Currency[Date], "mmm" ) = LEFT ( Sales[Date], 3 ) )
        )
    )
)