Dax measure circular dependecy

[michalnrf]

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 value for previous week. Every time when I'm trying prepare measure to calculate this I'm receiving circular dependecy problem. Can you give me some advice or show some solution how can I solve my problem? (manual example below)

Thank you in advance.

[v-shex-msft · Accepted solution]

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]

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-...

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/1447...