cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tuomas-i Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Using a certain value from a related table

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 )

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super User
Super User

Re: Using a certain value from a related table

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
tuomas-i Frequent Visitor
Frequent Visitor

Re: Using a certain value from a related table

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

 

 

 

 

Community Support Team
Community Support Team

Re: Using a certain value from a related table

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 )

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.