Reply
Frequent Visitor
Posts: 3
Registered: ‎03-22-2017

Values from two tables on same date axis without table connection

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!