Forum Discussion

DanielBI's avatar
DanielBI
Frequent Visitor
9 years ago

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 de...
Need Help
v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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's avatar
DanielBI
Frequent Visitor
9 years ago

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's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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])))