DAX: Avoid cyclic dependency

[Jaed]

Hi all,

 I am new to power BI.

I am trying to calculate the daily open pieces.

I am able to do this in excel, but when copieng to PBI I get a cyclic dependency.

Pieces requested = sum of all open pieces on a certain requested dispatch date

Pieces Shipped = sum of all pieces shipped on a certain loaded dispatch date

Backlog Formula = first row  // =B3-D3 and from there on =C4-D4

Pieces requested +Backlog = first row  // =B3 and from there on =B4+E3

I have tried working with offset, but only get a cyclic dependency.

Is there a solution doing it in this logic?

Much appreciate your help.

BR

[MFelix · Accepted solution]

Hi @Jaed ,

For this you need to create two different measures:

Pieces Requested  + Backlog =
VAR temptable =
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] < MAX ( 'Table'[Date] ) )
RETURN
    SUM ( 'Table'[Pieces Requested] )
        + SUMX ( temptable, 'Table'[Pieces Requested] - 'Table'[Pieces Shiped] )




Backlog =
VAR temptable =
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
RETURN
    SUMX ( temptable, 'Table'[Pieces Requested] - 'Table'[Pieces Shiped] )

If you want to have this based on a column redo the formulas to:

Backlog Column = 
VAR temptable =
    FILTER (  'Table', 'Table'[Date] <= EARLIER(  'Table'[Date] ) )
RETURN
    SUMX ( temptable, 'Table'[Pieces Requested] - 'Table'[Pieces Shiped] )

Pieces Requested  + Backlog Column = 
VAR temptable =
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] < EARLIER(  'Table'[Date] ) )
RETURN
     'Table'[Pieces Requested] 
        + SUMX ( temptable, 'Table'[Pieces Requested] - 'Table'[Pieces Shiped] )

[MFelix · Accepted solution]

Hi @Jaed ,

For this you need to create two different measures:

Pieces Requested  + Backlog =
VAR temptable =
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] < MAX ( 'Table'[Date] ) )
RETURN
    SUM ( 'Table'[Pieces Requested] )
        + SUMX ( temptable, 'Table'[Pieces Requested] - 'Table'[Pieces Shiped] )




Backlog =
VAR temptable =
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
RETURN
    SUMX ( temptable, 'Table'[Pieces Requested] - 'Table'[Pieces Shiped] )

If you want to have this based on a column redo the formulas to:

Backlog Column = 
VAR temptable =
    FILTER (  'Table', 'Table'[Date] <= EARLIER(  'Table'[Date] ) )
RETURN
    SUMX ( temptable, 'Table'[Pieces Requested] - 'Table'[Pieces Shiped] )

Pieces Requested  + Backlog Column = 
VAR temptable =
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] < EARLIER(  'Table'[Date] ) )
RETURN
     'Table'[Pieces Requested] 
        + SUMX ( temptable, 'Table'[Pieces Requested] - 'Table'[Pieces Shiped] )

[Jaed · Accepted solution]

Thank you very much 🙂