Sum up dates and multiply them with costs by year

[Euro_PBI_User]

Hi there,

I've been trying to find a measure that can show the following results in a line and clustered chart:

3 responses in the year 2020 * € 100,- = € 300,-.  This measure would be:

Saved costs = Calculate([Savings] * 100)  -- after I created the measure 'Savings' to sum up all response dates in the table below. 

But what if I want to select all years that have different costs per year, like:

3 responses in the year 2020 (€ 100,- per response)  + 4 responses in the year 2021 (€ 118,- per response) + 2 responses in the year 2022 (€ 115,- per response) = € 1002,- in total.

Is there a way it can recognize the year in the response date, and make the right calculation if I would use a separate table with just the costs and year?

I hope somebody can help me. Thank you!

Year	Saved cost
2020	€ 100
2021	€ 118
2022	€ 115

Office workers fill in the response date, so the measure shoud recognize how to make the right calculation.

	Response date	Saved costs
	6-6-2020	€      100,00
	17-6-2020	€      100,00
	20-12-2021	€      118,00
	6-4-2021	€      118,00
	27-3-2021	€      118,00
	12-4-2021	€      118,00
	11-4-2021	€      118,00
	3-3-2022	€      115,00
	28-2-2022	€      115,00

[Euro_PBI_User]

@johnt75 Thank you for the response.

I can't get the one-to-many relationship as you might see in the screenshot below. I've tried so many different ways to get the correct results. It does show a total costs  and a costs per year, but only 2022 is the correct total costs. Any idea how I could correct the model view? Thank you.

[johnt75]

Can you remove the relationships between Tab3 and Tab1 and create a relationship from Tab3[Year] to Calendar[Year] ?

[Euro_PBI_User]

I've followed your last instruction, but it still showed incorrect results. According to a colleague it was caused by my relationship between Sent letters and Date, and he created some other measures. It seems to work, but I still have learn how exactly.

Anyways, thanks again for your help johnt75. 🙂

[Euro_PBI_User]

I also used your measure of course. 🙂

Saved Costs = SUMX(

    'Tab1',

    RELATED( 'Tab3'[Saved cost])

)

[johnt75]

Link your Year Cost table to the date table in a one-to-many relationship and then you can create a measure like

Saved Costs = SUMX(
	'Responses',
	RELATED( 'Year Cost'[Saved cost])
)