Values from two tables on same date axis without table connection

[DanielBI]

Hey guys,

I am currently building a dashboard that shows actual sales and service execution figures in comparison with targets. I have two tables:

1. "Deals" - A CRM database with all company deals including the column "Deal Close Timestamp"

2. "Targets" - A Table with monthly targets for different KPIs (Sales, Services executed). It includes columns like "Value", "target date" and "Target Name".

I would like to show the targets and actuals on a monthly basis in a column chart. I know that I could create a date table for example and connect the two tables through this. I would like to prevent this however since this might cause conflicts if I want to link the "Targets" and "Deals" table through other tables at a later stage. I also have another use-case where I already can not connect two tables because of such a conflict.

That is why I am looking for a measure or something else to do the job. Is there a measure that would select the appropriate target based on the "Deal close Timestamp" from the "Deals" Table?

Something like:

monthlyTarget = calculate(sum(Targets[Value], Targets[Target Date] = Deals[Timestamp Deal Closed]))

which I could then just pull into the value field of a column chart that already shows actual deal value over the months and then shows the appropriate targets next to that?

Help would be greatly appreciated! 

[v-huizhn-msft]

Hi @DanielBI,

After research, you must not have a relationship between Deals table and Target.  Then create a measure using the formula.

monthlyTarget = calculate(sum(Targets[Value], Targets[Target Date] = MAX(Deals[Timestamp Deal Closed])))

Then add the Deal[month] as x-axis level, the measure as column value, and check if it works fine.

If this is not what you want, please post some sample data for further analysis.

Best Regards,
Angelia

[DanielBI]

Hey Angelia,

thank you for your reply! I just tried the solution and got an error. 

"A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

I think I am not understanding the formula context here correctly. Do you know what the issue might be?

Sample data attached.

"Deals" Table. There is also another column named "Value" which includes the deals value."Targets" Table. Ignore the blank on top. I summarized from the real table with random target values.

[v-huizhn-msft]

Hi @DanielBI,

Please try the formula below. It lost part in the formula above.

monthlyTarget = calculate(sum(Targets[Value]), FILTER(Targets,Targets[Target Date] = MAX(Deals[Timestamp Deal Closed])))