Forum Discussion

chaitanya2487's avatar
chaitanya2487
New Member
2 years ago
Solved

DAX previous value calculate row value

Hi 

 I'm in need of your assistance with a DAX function.

I'm facing challenges when it comes to calculating a column value based on the previous row's value.

Here's a sample dataset I'm working with:

week numberopen countclose countbacklog
30303240
312515 
325560 
334540 
343228 
352026 
362624 

In the dataset, I want to calculate the "backlog" column, which should be determined by the formula:

backlog = previous week's backlog + opencount - closecount.

For example, for week 31, the backlog should be calculated as the backlog of week 30 + opencount of week 31 - closecount of week 31.

I've attempted to create several DAX expressions, but I keep encountering circular dependency issues. Your assistance in resolving this matter would be greatly appreciated.

Thanks 
Chaitanya D

  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi, 

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

     

     

     

     

    Backlog measure: =
VAR _startweeknumber =
    CALCULATE ( MIN ( Data[week number] ), REMOVEFILTERS () )
VAR _currentrowweeknumber =
    MAX ( Data[week number] )
VAR _startbacklog = 40
RETURN
    IF (
        HASONEVALUE ( Data[week number] ),
        SWITCH (
            TRUE (),
            MAX ( Data[week number] ) = _startweeknumber, _startbacklog,
            CALCULATE (
                SUMX ( Data, Data[open count] - Data[close count] ),
                FILTER (
                    ALL ( Data ),
                    Data[week number] > _startweeknumber
                        && Data[week number] <= _currentrowweeknumber
                )
            ) + _startbacklog
        )
    )

     

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi, 

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

     

     

     

     

    Backlog measure: =
VAR _startweeknumber =
    CALCULATE ( MIN ( Data[week number] ), REMOVEFILTERS () )
VAR _currentrowweeknumber =
    MAX ( Data[week number] )
VAR _startbacklog = 40
RETURN
    IF (
        HASONEVALUE ( Data[week number] ),
        SWITCH (
            TRUE (),
            MAX ( Data[week number] ) = _startweeknumber, _startbacklog,
            CALCULATE (
                SUMX ( Data, Data[open count] - Data[close count] ),
                FILTER (
                    ALL ( Data ),
                    Data[week number] > _startweeknumber
                        && Data[week number] <= _currentrowweeknumber
                )
            ) + _startbacklog
        )
    )