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 ...
Need Help
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 ) )
        )
    )
)


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

      Hello

       

      The following statement delivers error.

      FILTER ( Currency, FORMAT ( Currency[Date], "mmm" ) = LEFT ( Sales[Date], 3 ) )

      The 'Sales[Date]' is not found. In the Sales[ ] , I can only find other calculated measures, not column data.

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

        That's weird.

        Did you use the whole formula? As a measure? You will not be able to use LEFT ( Sales[Date], 3 ) in a measure unless wrapped in a function such as SUMX that iterates a table/table expression. Can you please post a screenshot of the formula including the error?