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?
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!