Forum Discussion

michalnrf's avatar
michalnrf
Frequent Visitor
2 years ago
Solved

Dax measure circular dependecy

Hello, I'm trying to create two measures Available and End.  For first I'm taking start value and then add parameter A and substract parameter B equals End. Next week for Available I'm taking End va...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    HI michalnrf,

    You can try to use following measure formula if it suitable for your requirement:

    Available =
VAR _initValue =
    CALCULATE (
        MIN ( Table1[StartValue] ),
        FILTER ( ALLSELECTED ( Table1 ), [StartValue] <> BLANK () )
    )
VAR currYW =
    MAX ( Table1[Year Week] )
VAR rollingA =
    CALCULATE (
        SUM ( Table1[ParameterA] ),
        FILTER ( ALLSELECTED ( Table1 ), [Year Week] < currYW )
    )
VAR rollingB =
    CALCULATE (
        SUM ( Table1[ParameterB] ),
        FILTER ( ALLSELECTED ( Table1 ), [Year Week] < currYW )
    )
RETURN
    _initValue - rollingA + rollingB
    
End =
VAR _initValue =
    CALCULATE (
        MIN ( Table1[StartValue] ),
        FILTER ( ALLSELECTED ( Table1 ), [StartValue] <> BLANK () )
    )
VAR currYW =
    MAX ( Table1[Year Week] )
VAR rollingA =
    CALCULATE (
        SUM ( Table1[ParameterA] ),
        FILTER ( ALLSELECTED ( Table1 ), [Year Week] <= currYW )
    )
VAR rollingB =
    CALCULATE (
        SUM ( Table1[ParameterB] ),
        FILTER ( ALLSELECTED ( Table1 ), [Year Week] <= currYW )
    )
RETURN
    _initValue - rollingA + rollingB

    Regards,
    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
2 years ago

HI michalnrf,

You can try to use following measure formula if it suitable for your requirement:

Available =
VAR _initValue =
    CALCULATE (
        MIN ( Table1[StartValue] ),
        FILTER ( ALLSELECTED ( Table1 ), [StartValue] <> BLANK () )
    )
VAR currYW =
    MAX ( Table1[Year Week] )
VAR rollingA =
    CALCULATE (
        SUM ( Table1[ParameterA] ),
        FILTER ( ALLSELECTED ( Table1 ), [Year Week] < currYW )
    )
VAR rollingB =
    CALCULATE (
        SUM ( Table1[ParameterB] ),
        FILTER ( ALLSELECTED ( Table1 ), [Year Week] < currYW )
    )
RETURN
    _initValue - rollingA + rollingB
    
End =
VAR _initValue =
    CALCULATE (
        MIN ( Table1[StartValue] ),
        FILTER ( ALLSELECTED ( Table1 ), [StartValue] <> BLANK () )
    )
VAR currYW =
    MAX ( Table1[Year Week] )
VAR rollingA =
    CALCULATE (
        SUM ( Table1[ParameterA] ),
        FILTER ( ALLSELECTED ( Table1 ), [Year Week] <= currYW )
    )
VAR rollingB =
    CALCULATE (
        SUM ( Table1[ParameterB] ),
        FILTER ( ALLSELECTED ( Table1 ), [Year Week] <= currYW )
    )
RETURN
    _initValue - rollingA + rollingB

Regards,
Xiaoxin Sheng