Using a certain value from a related table

[tuomas-i]

I have two tables: Tasks and TaskBaselines

Tasks:
TaskId, TaskFinishDate

TaskBaselines:
TaskId, TaskBaselineNumber, TaskBaselineFinishDate

I would like to get a DATEDIFF (in days) between the TaskFinishDate and the related TaskBaseline which has the highest TaskBaselineNumber (meaning it is the last baseline created).

I have tried meddling with it for some time but can't seem to find a way.

Link to some mockdata: https://drive.google.com/open?id=1FCQpVKCAmMa8NnC_kB2P--qknxzca_lT

[v-yulgu-msft · Accepted solution]

Hi @tuomas-i,

Please create below measures:

highest baseline number =
CALCULATE (
    MAX ( TaskBaselines[BaselineNumber] ),
    ALLEXCEPT ( TaskBaselines, TaskBaselines[TaskId] )
)

Latestfinishtime =
CALCULATE (
    MAX ( TaskBaselines[TaskBaselineFinishDate] ),
    FILTER (
        ALL ( TaskBaselines ),
        TaskBaselines[BaselineNumber] = [highest baseline number]
            && TaskBaselines[TaskId] = SELECTEDVALUE ( Tasks[TaskId] )
    )
)

datediff =
DATEDIFF ( SELECTEDVALUE ( Tasks[TaskFinishDate] ), [Latestfinishtime], DAY )

Best regards,

Yuliana Gu

[CheenuSing]

Hi @tuomas-i

Please share some data on google drive or one-drive with the link posted here.  Also what is the exact output you desire.

Cheers

CheenuSing

[tuomas-i]

Hi @CheenuSing,

Added a mockdata link to the opening post.

So I have projects, and I want to see how each project's TaskFinishDate compares to last created (highest BaselineNumber) baseline's TaskBaselineFinishDate.

So basically, I want a table with the following format (only the last column is relevant though):

[ProjectName], [TaskName], [TaskFinishDate minus highest BaselineNumber TaskBaselineFinishDate (in days)]

Example Project, Example Task, -3

Thus, that example project's task is projected to finish 3 days ahead of schedule. Whether a measure or calculated column is used, does not matter (even though a measure might be more convenient since there are a lot of tasks and no point in calculating that for each one).