Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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])
Hi , @cmilligan262
Here is a demo.
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.
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])
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
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.
Hi @cmilligan262 ,
Try this code to create a calculated column: