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

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Using a certain value from a related table for calculation

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
2 REPLIES 2
Super User
Super User

Re: Using a certain value from a related table for calculation

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Highlighted
tuomas-i Frequent Visitor
Frequent Visitor

Re: Using a certain value from a related table for calculation

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

 

Capture.PNG