Forum Discussion

tuomas-i's avatar
tuomas-i
Frequent Visitor
7 years ago
Solved

Using a certain value from a related table

I have two tables: Tasks and TaskBaselines       Tasks: TaskId, TaskFinishDate TaskBaselines: TaskId, TaskBaselineNumber, TaskBaselineFinishDate     I would like to get a DATEDIFF (in days...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
tuomas-i
Frequent Visitor
7 years ago

Hi Anonymous,

 

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