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 c...
  • 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] )
        )
    )
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] )
        )
    )