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

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