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.
The following is sample data and would have rows with several years of monthly pulls of a roster and what Org (think department) every employee is in as of the end of each month. I want to create a column (shown as "Status Change") that evaluates each row against that same person in the next month to see if their Org changed or if they disappeared completely from the list. "Re-Alignment" would mean that their Org changes in the next period and "Departed" means it was their last month with the company since they were not on the roster in the next period. Blanks can be given for everything else.
File Date | Name | Org Tier 1 | Status Change |
10/31/2018 | Smith, John | 4.0 | |
11/30/2018 | Smith, John | 4.0 | |
12/31/2018 | Smith, John | 4.0 | |
1/31/2019 | Smith, John | 4.0 | |
2/28/2019 | Smith, John | 4.0 | |
3/31/2019 | Smith, John | 4.0 | |
4/30/2019 | Smith, John | 4.0 | |
5/31/2019 | Smith, John | 4.0 | Re-alignment |
6/30/2019 | Smith, John | 8.0 | |
7/31/2019 | Smith, John | 8.0 | |
8/31/2019 | Smith, John | 8.0 | |
9/30/2019 | Smith, John | 8.0 | |
10/31/2019 | Smith, John | 8.0 | |
11/30/2019 | Smith, John | 8.0 | Departed |
Solved! Go to Solution.
pls try to create a column
Column =
var org= MINX(FILTER(Sheet1,Sheet1[Name]=EARLIER(Sheet1[Name])&&'Sheet1'[File Date]>EARLIER(Sheet1[File Date])),Sheet1[Org Tier 1])
var _date=MAXX(FILTER(Sheet1,Sheet1[Name]=EARLIER(Sheet1[Name])),Sheet1[File Date])
return if(_date=Sheet1[File Date],"Departed",if(org=Sheet1[Org Tier 1],"","Re-alignment"))
Proud to be a Super User!
pls try to create a column
Column =
var org= MINX(FILTER(Sheet1,Sheet1[Name]=EARLIER(Sheet1[Name])&&'Sheet1'[File Date]>EARLIER(Sheet1[File Date])),Sheet1[Org Tier 1])
var _date=MAXX(FILTER(Sheet1,Sheet1[Name]=EARLIER(Sheet1[Name])),Sheet1[File Date])
return if(_date=Sheet1[File Date],"Departed",if(org=Sheet1[Org Tier 1],"","Re-alignment"))
Proud to be a Super User!
Thanks, Ryan - So that column function includes two measures that I need to create first?
That's one measure of column.
you can learn more about VAR function here
https://docs.microsoft.com/en-us/dax/var-dax
Proud to be a Super User!
Wow, that's amazing. I've never used thosed within column functions before. Thank you very much!
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |