cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
zombieug Regular Visitor
Regular Visitor

Calculate Column based on different tables

Hello forum,

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

 

I got the following two tables Lifecycle and Parts produced:

Lifecycle.pngLifecycle

Parts produced.pngParts 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
Super User

Re: Calculate Column based on different tables

Hi @zombieug

 

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
Super User

Re: Calculate Column based on different tables

Hi @zombieug

 

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]
            )
        )
    )
zombieug Regular Visitor
Regular Visitor

Re: Calculate Column based on different tables

@Zubair_Muhammad

As always flawless. Thank you sir.