Search code examples
powerbidaxpowerbi-desktopoffset

Error using offset to calculate previous row in DAX

I need to generate a column containing the previous value of the date column partition by group. This is an example of the table

Group   Date
A   01/02/2022
A   15/07/2022
A   28/11/2022
B   05/09/2022
B   06/10/2022
B   29/11/2022
B   19/12/2022
C   05/08/2022
C   15/12/2022
C   22/01/2023

To reach that I have created the new column using this DAX formula:

Previous Date = 
var groups = 'Sample'[Group]
var dates = 'Sample'[Date]
REturn
CALCULATE(
        MAX('Sample'[Date]),
        FILTER(
            'Sample',
            'Sample'[Date] < dates &&
            'Sample'[Group] = groups
        )     
)

This is working fine. However the idea is to use the OFFSET formula. But I'm not so sure why I'm getting always a circular dependency error:

OFfset Previous = 
CALCULATE(
    MAX('Sample'[Date]),
    OFFSET(
        -1,
        SUMMARIZE(ALLSELECTED('Sample'), 'Sample'[Date], 'Sample'[Group])
        ))

Or with this one

enter image description here

What am I missing in this calculation?

Solution

  • The following works for me.

    enter image description here

    Offset Previous = 
CALCULATE(
    MAX('Sample'[Date]),
    OFFSET(
        -1,
        ORDERBY('Sample'[Date]),
        PARTITIONBY([Group])
    ), 
    REMOVEFILTERS()
)

    Regarding your circular dependency, it is due to not taking the other calculated column out of the filter context. Calculated column 1 depends on calculated column 2 and vice versa hence the circular dependency. You can read more here: https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/