Forum Discussion

StrikerZero's avatar
StrikerZero
Frequent Visitor
4 years ago
Solved

Previous computation with range

Hi!
I need to calculate values that are accumulated if it is in a certain condition.
Basically, if the value of Control is different from 0, the value of
Duration is repeated in the sum_duration column, in case the value of Control_01 = 0 must be accumulated until the value of Control is different from 0.

I'm sending an example table with what I need to do.

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating measures.

     

     

    Sum duration control 1: = 
VAR _currentID =
    MAX ( Data[ID] )
VAR _previousIDcontrolnumberzero =
    MAXX (
        FILTER ( ALL ( Data ), Data[ID] < _currentID && Data[Control_01] <> 0 ),
        Data[ID]
    ) + 1
VAR _condition =
    MAXX ( FILTER ( ALL ( Data ), Data[ID] = _currentID - 1 ), Data[Control_01] )
RETURN
    IF (
        HASONEVALUE ( Data[ID] ),
        IF (
            _previousIDcontrolnumberzero <> BLANK ()
                && _condition = 0,
            CALCULATE (
                SUM ( Data[Duration] ),
                FILTER (
                    ALL ( Data ),
                    Data[ID] >= _previousIDcontrolnumberzero
                        && Data[ID] <= _currentID
                )
            ),
            SUM ( Data[Duration] )
        )
    )

     

    Sum duration control 2: = 
VAR _currentID =
    MAX ( Data[ID] )
VAR _previousIDcontrolnumberzero =
    MAXX (
        FILTER ( ALL ( Data ), Data[ID] < _currentID && Data[Control_02] <> 0 ),
        Data[ID]
    ) + 1
VAR _condition =
    MAXX ( FILTER ( ALL ( Data ), Data[ID] = _currentID - 1 ), Data[Control_02] )
RETURN
    IF (
        HASONEVALUE ( Data[ID] ),
        IF (
            _previousIDcontrolnumberzero <> BLANK ()
                && _condition = 0,
            CALCULATE (
                SUM ( Data[Duration] ),
                FILTER (
                    ALL ( Data ),
                    Data[ID] >= _previousIDcontrolnumberzero
                        && Data[ID] <= _currentID
                )
            ),
            SUM ( Data[Duration] )
        )
    )

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating measures.

     

     

    Sum duration control 1: = 
VAR _currentID =
    MAX ( Data[ID] )
VAR _previousIDcontrolnumberzero =
    MAXX (
        FILTER ( ALL ( Data ), Data[ID] < _currentID && Data[Control_01] <> 0 ),
        Data[ID]
    ) + 1
VAR _condition =
    MAXX ( FILTER ( ALL ( Data ), Data[ID] = _currentID - 1 ), Data[Control_01] )
RETURN
    IF (
        HASONEVALUE ( Data[ID] ),
        IF (
            _previousIDcontrolnumberzero <> BLANK ()
                && _condition = 0,
            CALCULATE (
                SUM ( Data[Duration] ),
                FILTER (
                    ALL ( Data ),
                    Data[ID] >= _previousIDcontrolnumberzero
                        && Data[ID] <= _currentID
                )
            ),
            SUM ( Data[Duration] )
        )
    )

     

    Sum duration control 2: = 
VAR _currentID =
    MAX ( Data[ID] )
VAR _previousIDcontrolnumberzero =
    MAXX (
        FILTER ( ALL ( Data ), Data[ID] < _currentID && Data[Control_02] <> 0 ),
        Data[ID]
    ) + 1
VAR _condition =
    MAXX ( FILTER ( ALL ( Data ), Data[ID] = _currentID - 1 ), Data[Control_02] )
RETURN
    IF (
        HASONEVALUE ( Data[ID] ),
        IF (
            _previousIDcontrolnumberzero <> BLANK ()
                && _condition = 0,
            CALCULATE (
                SUM ( Data[Duration] ),
                FILTER (
                    ALL ( Data ),
                    Data[ID] >= _previousIDcontrolnumberzero
                        && Data[ID] <= _currentID
                )
            ),
            SUM ( Data[Duration] )
        )
    )