So I have two columns that I need to add up and divide. The problem is that each one requires a different date range dimension to sum and have the proper outcome so the final division turns out correct.

First value:
It’s just a sum of the total amount of appointments that a certain bussines got in a period. To properly sum this value I need to use an specific date range dimension, let’s call it “date of creation”, representing the exact date that the appointment was created and set.

Second value:
Pretty much the same thing that the first value but in this case I need to sum up the income that each appoint generated to the business, the problem lies in the fact that I must sum the income based on another date range dimension, let’s call it, “sale closure date”.

These two values, both income and appointment count are on the same table, and on the same rows. So I can just make a graph with a calculated field that sums both values up and divides them, but the results it’s always off because it’s only possible to assing one date range dimension for the whole calculation and to for each sum individualy.

Formula for the new metric:
IF(Currency=”Peso”,(SUM(importe.)*Tasa de cambio) /(SUM(Agendamiento)+SUM(Venta_Ganada)),Importe Total /(SUM(Agendamiento)+SUM(Venta_Ganada) ))

— Ignore the conditional IF, i’m only using it to implement currency options—

I need to sum “importe” or income as i’ve previously stated based the date range dimension “sale closure date” and divide it by the sum of “Agendamiento” or appointments, wich has to be added up using a different date range dimension called “date of creation”.

I know this particular problem is caused by a very poor data structure but i can’t change that now, no matter how much i’d like.

I’ve never asked for help like this before so please, any idea on how to solve this or question that could help me explain my problem better, it’s appreciated!

sorry for the occasional broken english!

IF(Currency=”Peso”,(SUM(importe.)*Tasa de cambio) /(SUM(Agendamiento)+SUM(Venta_Ganada)),Importe Total /(SUM(Agendamiento)+SUM(Venta_Ganada) ))