Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dax Experts,
In short, I’m calculating retention during a time period. In order to make sure transfers into a department don’t affect my retention rate for a department, I’m looking to remove transfers from my remaining count. One of the initial steps though is identifying someone that transferred departments. My thought was to create a calculated column the looks up the DeptDesc value for an employee row 1 year or 1 month earlier. I would then compare the employee’s deptdesc in the end period to the start period (i.e. 1 year or 1 month ago) and if they match include them in my retention calc, else exclude.
Where I’m stuck: Get/Lookup the deptdesc 1 month earlier in the same table for the employee row.
Anticipated results with table structure in column “LastMonthDept”
Table Name = Turnover
*Note: there is a date table linked to the period column that don’t feel it’s necessary for the calculation.
Employee ID | DeptDesc | Period | LastMonthDept |
12345 | Dept 10 | 10/31/2021 | Dept 10 |
12346 | Dept 11 | 10/31/2021 | Dept 11 |
12347 | Dept 10 | 10/31/2021 | Dept 10 |
12348 | Dept 12 | 10/31/2021 | Dept 12 |
12349 | Dept 10 | 10/31/2021 | Dept 10 |
12350 | Dept 9 | 10/31/2021 | Dept 9 |
12351 | Dept 14 | 10/31/2021 | Dept 14 |
12352 | Dept 10 | 10/31/2021 | Dept 10 |
12345 | Dept 10 | 11/31/2021 | Dept 10 |
12346 | Dept 10 | 11/31/2021 | Dept 11 |
12347 | Dept 10 | 11/31/2021 | Dept 10 |
12348 | Dept 10 | 11/31/2021 | Dept 12 |
12349 | Dept 10 | 11/31/2021 | Dept 10 |
12350 | Dept 14 | 11/31/2021 | Dept 9 |
12351 | Dept 14 | 11/31/2021 | Dept 14 |
12352 | Dept 10 | 11/31/2021 | Dept 10 |
Failed Attempts:
LastMonthDept =
VAR PM = CALCULATE(PREVIOUSMONTH(Turnover[Period]),Turnover[Period])
RETURN
LOOKUPVALUE(
Turnover[DeptDesc],
Turnover[Employee ID],Turnover[Employee ID],
Turnover[Period],PM
)
LastMonthDept =
maxx(filter(Turnover,Turnover[Employee ID] = earlier(Turnover[Employee ID]) && Turnover[Period] = EARLIER(Turnover[Period])-30),Turnover[DeptDesc])
Thank you in advance!
Solved! Go to Solution.
Hi, @bwarner87
Accord to your description, I think you can create a measure.
Like this:
LastMonthDept =
VAR a = Turnover[Period]
VAR b =
MAXX (
FILTER (
Turnover,
Turnover[Employee ID] = EARLIER ( Turnover[Employee ID] )
&& Turnover[Period]
= DATE ( YEAR ( a ) - 1, MONTH ( a ), DAY ( a ) )
),
Turnover[DeptDesc]
)
RETURN
IF ( b = BLANK (), Turnover[DeptDesc], b )
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
Hi, @bwarner87
Accord to your description, I think you can create a measure.
Like this:
LastMonthDept =
VAR a = Turnover[Period]
VAR b =
MAXX (
FILTER (
Turnover,
Turnover[Employee ID] = EARLIER ( Turnover[Employee ID] )
&& Turnover[Period]
= DATE ( YEAR ( a ) - 1, MONTH ( a ), DAY ( a ) )
),
Turnover[DeptDesc]
)
RETURN
IF ( b = BLANK (), Turnover[DeptDesc], b )
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
Thank you so much for this. I can verify that it does work for my use cases.
I'll try to explain in layman's terms as an exercise to help my understanding.
First you created a variable that stores a filtered table accounting for it matching the employee's ID AND that all period dates are in the past or less than the period is that row (i.e. earlier(turnover[period]).
you then used that variable in a return expression. where ou passwed in the table of rows for the employee and took the top row (i.e. TOPN 1) which is going to be the previous month because you ommitted an order value and so it would have the most recent date behine the value of the period in the row. You then used Maxx to retun the description from that row.
To make sure that I understood this I modified your DAX to grab the department 1 year earlier. I tried 2 methods but not able to get the 1 year. Can you help?
Failed Attempt # 1 -> I simply though i'd expand the returned rows to 12 and then take the MIN since it's ordered by descending. However,the only 2 aggregates (max and Min) will now be selecting from a mix of departments of a 12 month period and pulling back either the first or last based on alphabetical order, which is incorrect.
LY Dept =
VAR _lm =
FILTER(
Turnover,
Turnover[Employee ID] = EARLIER( Turnover[Employee ID] )
&& Turnover[Period] < EARLIER( Turnover[Period] )
)
RETURN
IF(
ISEMPTY( _lm ),
Turnover[DeptDesc],
MINX( TOPN( 12, _lm, Turnover[Period] ), Turnover[DeptDesc] )
)
Failed Attempe #2: I thought maybe int he filter expression i could manipulate the list of [period] dates to start 1 year earlier and then grab that max date. This gave me somehow the latest department which is incorrect.
LY Dept =
VAR _lm =
FILTER(
Turnover,
Turnover[Employee ID] = EARLIER( Turnover[Employee ID] )
&& MAXX(DATESINPERIOD(Turnover[Period],EARLIER(Turnover[Period]),-1,YEAR),Turnover[Period])
)
RETURN
IF(
ISEMPTY( _lm ),
Turnover[DeptDesc],
MAXX( TOPN( 1, _lm, Turnover[Period] ), Turnover[DeptDesc] )
)
Failed Attempt #3: I then thought if I could create 2 variables representing 11 rows and 12 rows sorted by PERIOD in descending order i could extract out the 12th row by using the EXCEPT function. However, when i tried this it's giving me a circular dependency issue which I'm not sure how to remediate.
LY Dept =
VAR _lm =
FILTER(
Turnover,
Turnover[Employee ID] = EARLIER( Turnover[Employee ID] )
&& Turnover[Period] < EARLIER( Turnover[Period] )
)
VAR TBL12 =
TOPN( 12, _lm, Turnover[Period] )
VAR TBL11 =
TOPN( 11, _lm, Turnover[Period] )
RETURN
IF(
ISEMPTY(_lm),
Turnover[DeptDesc],
MAXX(EXCEPT(TBL11,TBL12), Turnover[DeptDesc])
)
Any suggestions on how to get 1 year back on the DeptDesc instead of 1 month?
LM Dept =
VAR _lm =
FILTER(
Turnover,
Turnover[Employee ID] = EARLIER( Turnover[Employee ID] )
&& Turnover[Period] < EARLIER( Turnover[Period] )
)
RETURN
IF(
ISEMPTY( _lm ),
Turnover[DeptDesc],
MAXX( TOPN( 1, _lm, Turnover[Period] ), Turnover[DeptDesc] )
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |