Divide value by specific monthly value in another table

[juangomez]

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.

Version	Date	to US
FCST 1 2020	1/01/2020	3,190.3780
FCST 1 2020	1/02/2020	3,190.3780
FCST 1 2020	1/03/2020	3,190.3780
FCST 1 2020	1/04/2020	3,190.3780
FCST 1 2020	1/05/2020	3,190.3780
FCST 1 2020	1/06/2020	3,190.3780
FCST 2 2020	1/01/2020	2,943.6855
FCST 2 2020	1/02/2020	2,877.5920
FCST 2 2020	1/03/2020	2,939.0248
FCST 2 2020	1/04/2020	2,871.8590
FCST 2 2020	1/05/2020	2,924.0452
2019	1/01/2019	2,872.4117
2019	1/02/2019	2,856.5965
2019	1/03/2019	2,846.0773
2019	1/04/2019	2,766.8433
2019	1/05/2019	2,856.7091
2019	1/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.

[amitchandak]

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]

Hello

Thanks for the quick response.

I believe my request was not clear enough.

Table 'Sales' has monthly sales 

Date	Sales
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'.

Version	Date	to US
FCST 1 2020	1/01/2020	3,190.3780
FCST 1 2020	1/02/2020	3,190.3780
FCST 1 2020	1/03/2020	3,190.3780
FCST 1 2020	1/04/2020	3,190.3780
FCST 1 2020	1/05/2020	3,190.3780
FCST 1 2020	1/06/2020	3,190.3780
FCST 2 2020	1/01/2020	2,943.6855
FCST 2 2020	1/02/2020	2,877.5920
FCST 2 2020	1/03/2020	2,939.0248
FCST 2 2020	1/04/2020	2,871.8590
FCST 2 2020	1/05/2020	2,924.0452
2019	1/01/2019	2,872.4117
2019	1/02/2019	2,856.5965
2019	1/03/2019	2,846.0773
2019	1/04/2019	2,766.8433
2019	1/05/2019	2,856.7091
2019	1/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:

Date	Sales	FCST 1 2020	US Sales
ene-19	100.00	3,190.38	0.03
feb-19	200.00	3,190.38	0.06
mar-19	300.00	3,190.38	0.09
abr-19	400.00	3,190.38	0.13
may-19	500.00	3,190.38	0.16
jun-19	600.00	3,190.38	0.19
ene-20	1,300.00	3,190.38	0.41
feb-20	1,400.00	3,190.38	0.44
mar-20	1,500.00	3,190.38	0.47
abr-20	1,600.00	3,190.38	0.50
may-20	1,700.00	3,190.38	0.53
jun-20	1,700.00	3,190.38	0.53

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

Date	Sales	FCST 2 2020	US Sales
ene-19	100.00	2,943.69	0.03
feb-19	200.00	2,877.59	0.07
mar-19	300.00	2,939.02	0.10
abr-19	400.00	2,871.86	0.14
may-19	500.00	2,924.05	0.17
jun-19	600.00	2,872.41	0.21
ene-20	1,300.00	2,943.69	0.44
feb-20	1,400.00	2,877.59	0.49
mar-20	1,500.00	2,939.02	0.51
abr-20	1,600.00	2,871.86	0.56
may-20	1,700.00	2,924.05	0.58
jun-20	1,700.00	2,872.41	0.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]

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]

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]

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? 

[juangomez]

Thanks for your help.

I did use the whole formula but kept receiving the error.

I finally could get the correct result with the following code:

// Create table Forex to identify slicer filter
// in Currency Table Create column Month= month(Date) as number
// in Sales Table Create column Month= month(Date) as number

US Sales= 
SUMX (
 // Get sales according to additional Sales Scenario slicer
    FILTER(Sales,Sales[Version]='Scenario 1'[MonthFilter 1 Value]),
    DIVIDE (
        //Sales in sales table with Sales Scenario filter
        Sales[Sales],
        SUMX(
            FILTER(
                //Double Filter,:
                //First filter for the version according to slicer
                FILTER(Currency,Currency[Version]=Forex[MonthFilter FX Value]),
                // Filter for an additional column
                Currency[Month]=Sales[Month]),
                // Return Currency Rate
                Currency[to US]
            )
        )
)