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: I would like to calculate the column Lifecycle (p...
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]
            )
        )
    )
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]
            )
        )
    )