Forum Discussion

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

Using a certain value from a related table for calculation

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.

 

  • TomMartens's avatar
    TomMartens
    7 years ago

    Hey,

    first I created a calculated column in the table TaskBaselines, that flags the highest baselinenumber per task:

    is max Baseline = 
var currentTaskID = 'TaskBaselines'[TaskID]
return
IF(
    CALCULATE(
        MAX('TaskBaselines'[TaskBaselineNumber])
        ,FILTER(
            ALL(TaskBaselines)
            ,TaskBaselines[TaskID] = currentTaskID
        )
    )
    = TaskBaselines[TaskBaselineNumber]
    ,"is max"
    ,"is not max"
)

    Then I created a calculated column in table tasks that calculates the difference in days using DATEDIFF, by pulling the date from TaskBaseline table utilizing the "is max Baseline" column like so:

    diff days = 
DATEDIFF(
'Tasks'[TaskFinishDate]
,LOOKUPVALUE(
    'TaskBaselines'[TaskBaselineFinishDate]
    ,TaskBaselines[TaskID],'Tasks'[TaskID]
    ,TaskBaselines[is max Baseline], "is max"
)

,DAY
)

    Hopefully this is what you are looking for.

     

    Regards,

    Tom

2 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    7 years ago

    Hey,

    first I created a calculated column in the table TaskBaselines, that flags the highest baselinenumber per task:

    is max Baseline = 
var currentTaskID = 'TaskBaselines'[TaskID]
return
IF(
    CALCULATE(
        MAX('TaskBaselines'[TaskBaselineNumber])
        ,FILTER(
            ALL(TaskBaselines)
            ,TaskBaselines[TaskID] = currentTaskID
        )
    )
    = TaskBaselines[TaskBaselineNumber]
    ,"is max"
    ,"is not max"
)

    Then I created a calculated column in table tasks that calculates the difference in days using DATEDIFF, by pulling the date from TaskBaseline table utilizing the "is max Baseline" column like so:

    diff days = 
DATEDIFF(
'Tasks'[TaskFinishDate]
,LOOKUPVALUE(
    'TaskBaselines'[TaskBaselineFinishDate]
    ,TaskBaselines[TaskID],'Tasks'[TaskID]
    ,TaskBaselines[is max Baseline], "is max"
)

,DAY
)

    Hopefully this is what you are looking for.

     

    Regards,

    Tom

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

      Hi,

       

      That seems to work, thank you! Previously I accomplished to compare the last baseline by date (not last created baseline). I figured I had done something wrong, since the DATEDIFFs did not match. However, even with your calculations, I'm having the same problem.

       

      I did step-by-step recalculations to the BaselineFinishDates and TaskFinishDates (in the picture below). Then I used them to calculate a new column:

       

       

      BaselineMinusFinishDateCheck = DATEDIFF(Tasks[BaselineCheck];Tasks[TaskFinishCheck];DAY)

       

      So those two rows should have the same data used in them (at least they appear exactly the same), but the DATEDIFF gives two different results. What gives? Should I make a new thread about this?

       

      Okay, I realized I should probably solve this one another way, or in another thread. However, I already checked that the data types / formats are the same both in query and in model, so I'm kind of baffled.

       

      --> Solved this one: had Sum on in the value fields, and changed it to don't summarize. (First I checked if the data in the table view was correct, and as it was, I figured the problem has to be in the visualization.)