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.
Hi all!
I have a bit of a pickle that I hope I can get help with!
I'm going to use a SCD to ensure my report can take into account organisational changes and moves.
Once I finalised my report - I realised that my start date shows Organisational start date, regardless of employees moving onto a new division. How can I get a column that shows the "division start date"?
I.e. How can I show that in below table, Carl's start date in Division A is 2017-01-01 as seen in the report. But Start date for Division B is the end date from his Division A row.
Or like my example with Lisa. Her start date for Division D should be her end date for Division C, and her start date for Division G should be her end date from Division D
Index | Division | Start date | End date | Name | ID sale |
1 | A | 2017-01-01 | 2019-01-01 | Carl | XXX |
2 | B | 2017-01-01 | 2022-01-01 | Carl | XXX |
3 | C | 2000-01-01 | 2018-05- 05 | Lisa | VVV |
4 | D | 2000-01-01 | 2020-03-03 | Lisa | VVV |
5 | G | 2000-01-01 | 2022-03-29 | Lisa | VVV |
Solved! Go to Solution.
The code is relying on the index column to be in ascending chronological order, and does give the correct results I think with the sample data
If that's not the case then it could be adapted to rely on the end date column instead, both for filtering out only those rows prior to the current one and for ordering the rows for the TOPN. That would also clear up the problem of adding the last end date as a start date.
To solve the 2nd problem, you could add another filter into the CALCULATETABLE so that it would be
CALCULATETABLE('Table', ALLEXCEPT('Table','Table'[Name]),'Table'[Index] < currentIndex, 'Table'[Start date] = currentStartDate)
Thanks for your help! With this solution though, there are two unwanted issues.
1. It gave inconsistent outcomes as it does not connect the dates chronologically. Meaning instead of an outcome similar to 2000-2018, 2018-2020, 2020-2022 it gives a column with 2000-2018, 2022-2020, 2022-2020. Example from my data:
Index | Division | Start date | End date | Name | ID sale | Division Start date (New) |
3 | C | 2000-01-01 | 2018-05- 05 | Lisa | VVV | 2000-01-01 |
4 | D | 2000-01-01 | 2020-03-03 | Lisa | VVV | 2022-03-29 |
5 | G | 2000-01-01 | 2022-03-29 | Lisa | VVV | 2020-03-03 |
2. A few employees have left and then come back to the organisation. In these scenarios I would want the start date to be taken from the actual start date column rather than from end date. This is how it shows now
Index | Division | Start date | End date | Name | ID sale | Division Start date (New) |
1 | A | 1994-01-01 | 2019-04-30 | John | VVV | 1994-01-01 |
2 | B | 2021-01-01 | 2022-01-01 | John | VVV | 2019-04-30 |
+ an extra: It also adds the last end date for an employee as a start date, which it should not do 🙂
Thanks!
The code is relying on the index column to be in ascending chronological order, and does give the correct results I think with the sample data
If that's not the case then it could be adapted to rely on the end date column instead, both for filtering out only those rows prior to the current one and for ordering the rows for the TOPN. That would also clear up the problem of adding the last end date as a start date.
To solve the 2nd problem, you could add another filter into the CALCULATETABLE so that it would be
CALCULATETABLE('Table', ALLEXCEPT('Table','Table'[Name]),'Table'[Index] < currentIndex, 'Table'[Start date] = currentStartDate)
This worked perfectly! Relying on the end date column that is.
Now I'm realising I've got other challenges in my model - but this will definitely help out once I've fixed it.
Thanks!
You can add a new column like
Division Start Date =
var currentIndex = 'Table'[Index]
var currentStartDate = 'Table'[Start date]
var prevEndDate = SELECTCOLUMNS(
TOPN(1, CALCULATETABLE('Table', ALLEXCEPT('Table','Table'[Name]),'Table'[Index] < currentIndex), 'Table'[Index], DESC),
"@val", 'Table'[End date]
)
return IF( NOT( ISBLANK( prevEndDate)), prevEndDate, currentStartDate)
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 |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |