Forum Discussion

zombieug's avatar
zombieug
Helper I
8 years ago
Solved

Calculate Column based on different tables

Hello forum,

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

 

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.

 

 

 

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

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