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

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

3 Replies

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

       

       

       

       

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    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