04-21-2017 07:53 AM
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!
04-23-2017 11:24 PM
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.
04-26-2017 01:59 AM
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.
04-27-2017 03:11 AM
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])))