Prior month Value Calculated Column

[cmilligan262]

I need to get movement in our roster and need to pull in the prior months cost center name in a calculcated column

This is what my data looks like

Date                           EMPID                        Cost Center Name

2/29/2020                  001                             Executive

2/29/2020                  002                             Apps

1/31/2020                  001                             Apps

1/31/2020                  002                             Apps

Ideally the prior cost center column would show Apps for Emp 001 for 2/29/2020

I've tried Calculate(Values(Cost Center Name),Filter(Table,EMPID=Earlier(EMPID)),DATEADD(Date Table(Date,-1,Month))

This returns that there is a circular dependency. 

Thank you in advance for the help.

[v-easonf-msft]

Hi , @cmilligan262 

Here is a demo.

Pbix attached

Try to change your  measure as below:

The prior cost center column =
VAR d = [Date]
RETURN
    CALCULATE (
        MAX ( 'Table'[Cost Center Name] ),
        FILTER (
            'Table',
            'Table'[EMPID] = EARLIER ( 'Table'[EMPID] )
                && 'Table'[Date]
                    >= DATE ( YEAR ( d ), MONTH ( d ) - 1, 1 )
                && 'Table'[Date]
                    <= DATE ( YEAR ( d ), MONTH ( d ), 1 ) - 1
        )
    )

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler · Accepted solution]

Perhaps:

Column =
  VAR __PreviousDate = MAXX(FILTER('Table',[Date]<EARLIER([Date])&&[EMPID]=EARLIER([EMPID])),[Date])
RETURN
  MAXX(FILTER('Table',[EMPID]=EARLIER([EMPID]) && [Date]=__PreviousDate),[Cost Center Name])

[cmilligan262]

Thank you @Greg_Deckler that worked.

Do you have any explanation on why that would work and why I was getting a circular dependecy error on the formula I tried

[Greg_Deckler]

I believe the short answer is that you were using CACULATE in a column formula along with DISTINCT or VALUES and that tends to be bad. @edhans probably has a much deeper and more insightful explanation as I have seen a really great explanation in a separate thread. @edhans would highly recommend making that a blog article.

[camargos88]

Hi @cmilligan262 ,

Try this code to create a calculated column:

Pior_Cost_Center =

VAR _date = 'Table (2)'[Date]

VAR _maxDate = CALCULATE(MAX('Table (2)'[Date]); FILTER(ALLEXCEPT('Table (2)';'Table (2)'[EMPID]); 'Table (2)'[Date] < _date))

RETURN CALCULATE(DISTINCT('Table (2)'[Cost Center Name]); FILTER(ALLEXCEPT('Table (2)';'Table (2)'[EMPID]); 'Table (2)'[Date] = _maxDate))

Ricardo