cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Calculate Column based on different tables

Hello forum,

I hope you can help me on this one...

I got the following two tables Lifecycle and Parts produced:

Lifecycle

Parts produced

I would like to calculate the column Lifecycle (parts produced) in the Lifecycle table.

It should be based on the Action 'Install', Partnumber as well as Line.

For the green example:

Partnumber 0804 431 961 was installed on 1st Jan and 10th Jan --> Lifcecycle from 1/1/2018 - 1/10/2018

Partnumber 0804 431 961 belongs to Line 71 --> Parts produced table gives me the information that Line 71 produced a total of 2350 parts during the Lifcycle interval.

Is it possible to calculate such a column?

If what I am trying to do is not clear to you or your need further information please let me know.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Calculate Column based on different tables

Try this calculated column in LifeCycle

```LifeCycleProduction =
VAR PreviousInstallDate =
CALCULATE (
MAX ( Lifecycle[Date] ),
FILTER (
ALLEXCEPT ( Lifecycle, Lifecycle[PartNumber] ),
Lifecycle[Actions] = "Install"
&& Lifecycle[Date] < EARLIER ( Lifecycle[Date] )
)
)
RETURN
IF (
Lifecycle[Actions] = "Install"
&& NOT ( ISBLANK ( PreviousInstallDate ) ),
CALCULATE (
SUM ( PartsProduced[Partsproduced] ),
FILTER (
PartsProduced,
PartsProduced[Line] = Lifecycle[Line]
&& PartsProduced[Date] >= PreviousInstallDate
&& PartsProduced[Date] <= Lifecycle[Date]
)
)
)```
2 REPLIES 2
Super User

## Re: Calculate Column based on different tables

Try this calculated column in LifeCycle

```LifeCycleProduction =
VAR PreviousInstallDate =
CALCULATE (
MAX ( Lifecycle[Date] ),
FILTER (
ALLEXCEPT ( Lifecycle, Lifecycle[PartNumber] ),
Lifecycle[Actions] = "Install"
&& Lifecycle[Date] < EARLIER ( Lifecycle[Date] )
)
)
RETURN
IF (
Lifecycle[Actions] = "Install"
&& NOT ( ISBLANK ( PreviousInstallDate ) ),
CALCULATE (
SUM ( PartsProduced[Partsproduced] ),
FILTER (
PartsProduced,
PartsProduced[Line] = Lifecycle[Line]
&& PartsProduced[Date] >= PreviousInstallDate
&& PartsProduced[Date] <= Lifecycle[Date]
)
)
)```
Regular Visitor