Calculate aggregate value based on filtering another table

[Matt22365]

Hi

I hope someone can help

I am using 2 tables from MS Project in Power BI, Tasks and Task Baseline

I would like to aggregate the costs column from "Tasks" against the task baseline finish date from "Task Baseline" into a line chart

I have managed to do this to a different date column in the same table, but cannot get it to work between tables. 

This is the formula which works (within the same table):

Running Total = CALCULATE(SUM(Tasks[TaskFixedCost]),FILTER(ALLSELECTED(tasks),Tasks[TaskFinishDate] <=MAX(Tasks[TaskFinishDate])))

but rather than Tasks[TaskFinshDate] I need it to be TaskBaselines[TaskBaselineFinishDate]

I have a 2 way relationship between these 2 tables and the data presents correctly in a table visual, I just cannot get the aggregate to work

Any help would be greatly appreciated

Thanks

Matt 

[Matt22365 · Accepted solution]

Thanks for everyones help

The simplist fix I found was to merge the 2 tables based on task ID. This puts both the finish date and baseline finish date in the same table so I could then do the calculation

Thanks

Matt 

[amitchandak]

@Matt22365 , refer how to use two dates using date table and userelation

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[vanessafvg]

can you screen shot your relationships?

[Matt22365]

Hi

Relationship screenshot as requested.

TaskBaselines - Tasks -> TaskID

Projects - Tasks -> ProjectID

Thanks

Matt 

[vanessafvg]

have you tried using the related function? test =
CALCULATE (
SUM ( Tasks[TaskFixedCost] ),
FILTER (
ALLSELECTED ( tasks ),
RELATED ( TaskBaselines[TaskBaselineFinishDate] )
<= MAX ( TaskBaselines[TaskBaselineFinishDate] )
)
)

if you provide some dummy data i can check what i am doing, otherwise give that a bash

[Matt22365]

Hi

Thanks. Unfortunately it wont let me use related between these 2 tables.

I cannot link you to data due to my companies security, however essentially the key information from the 2 tables is:

Tasks:

 - TaskID

 - TaskFixedCost

 - TaskFinishDate

TaskBaselines:

 - TaskID

 - TaskBaselineFinishDate

So I need to aggreate TaskFixedCost and plot it againt TaskFinishDate

Thanks

Matt 

[vanessafvg]

what error do you get? do you need all your bidirectional relationships?