Forum Discussion

cmilligan262's avatar
cmilligan262
Helper II
6 years ago
Solved

Prior month Value Calculated Column

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.

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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])

     

      

5 Replies

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    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
     
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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's avatar
      cmilligan262
      Helper II
      6 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        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.

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    6 years ago

    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.