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

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

You should be able to cover this specific scenario with SUMX.

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523