Help with measure combining dynamic data with fixed data

[Machiavel1469]

Hello!

I have the table with these columns for which I use this measure to calculate the weighted average Days to Pay by country:

Days to Pay WA= CALCULATE(DIVIDE(SUMX('Vendor Details','Vendor Details'[Spend]*'Vendor Details'[Days to Pay]),SUMX('Vendor Details','Vendor Details'[Spend])))

The problem is that I also have another table in which I have the Spend for the previous year for each country and I will also need to recalculate the days to pay based on the previous year spend:

Spend for Previous Year

Is there any measure through which I can calculate the current Days to Pay (with my measure) but to change the current spend with full year spend from the second table?

I am looking for a measure that would be dynamic and would show the results for each country like in this formula: Days to Pay by Country LY = (Days to Pay * Spend Previous Year)/ Spend Previous Year.

Can anyone help with this?

Thanks!

[Floriankx]

Is Country unique in both tables?

First of all I would relate the two country columns.

Then create a measure Spend PY:=Sum(Table2[Spend Previous Year]).

I the first table you can create a calculated colum with a formula similar the one you presented

Days to Pay by Country LY = (Days to Pay * Spend Previous Year)/ Spend Previous Year

But according to my math knowledge bold print passage allways equals 1.

But maybe you mean something like this:

Days to Pay by Country LY = [Days to Pay] * [Spend]/ [Spend PY]

Maybe you can give as an example of the expected result.

[Machiavel1469]

Hi,

The Country is unique only in the second table - the one with the spend by country for the last year.

In the first table I have the spend for each vendor based on which I calculate the days to pay by country with the measure I have mentioned above.

Sorry, for the incorrect formula! Actually it should be a SUM(Days to Pay*Spend Previous Year)/SUM(Spend Previous Year). But the Days to Pay from my measure.

The Total Days to Pay calculated from this formual should be different to what I get in the normal measure for the current year as i need to compare by weighing the days to pay both with the current spend and last year's spend.

Please check below the tables with the current results and the result I would expect for the total when weighing by the spend from the previous year:

Current year Days to Pay      Total Days to Pay based on Previous Year Spend

In the supplier table I have thousands of rows for supplier's spend and my measure for Days to Pay works just fine, but I will also need to have a comparison between the total current days to pay and the total days to pay weighed by the previous year's spend.

Let me know if this is clearer.

[Floriankx]

Except the total row i don't see a difference in Day to Pay so I don't get problem precisely.

If you had a country with last years spend of 100,000 and this year 120,000; Days to Pay are 50 days.

What would be your expected value for weighted Days to Pay?

Maybe if I understand this I can help you better.

[Machiavel1469]

Hello,

I know I'm not making myself very clear on this topic.

The issue I have is that the database I am using consists of rows with invoice details for each country. The overall result for the country is retrieved through this formula Days to Pay = CALCULATE(DIVIDE(SUMX('Vendor Details','Vendor Details'[Amount in Target Currency]*'Vendor Details'[WA Days to Pay]),SUMX('Vendor Details','Vendor Details'[Amount in Target Currency])))

This is a weighted average of the days to pay for the country (the days to pay for each document is weighted by amount). Basically it replicates a SUMPRODUCT formula from excel.

The database consists of the document for 2018 and 2017.

I need to see the global result based on this formula but in order to ensure the comparison between the two years I want to have a value of the current days to pay that are weighed with the spend for the previous year.

In the last column I have the values for this year calculated for each country based on the formula above. However I will also need to make a new weighted average based on the days to pay values for this year based on the spend from last year. Days to Pay and the spend are all measures. So, the difficulty I am facing is that the new measure for Days to Pay should consist of the standar measure for days to pay (formula above) but it needs to be weighed on the spend for last year:

 Days to Pay NEW = SUMX(Days to Pay*Spend Previous year)/SUMX(Spend Previous Year)  but the Days to Pay measure in here should actually calculate only the values for this year that need to be re-evaluated based on last year's spend.

Does this make any sense?

Spend Previous Year	Region	63,2
433,3	Brazil	59,3
124,9	Canada	40,0
187,5	Mexico	70,8
99,6	Chile	69,4
22,0	Colombia	63,5
114,8	Nigeria	75,5
142,8	South Africa	70,6

Thanks