Forum Discussion
Helper IMeasure for get current status employee
Hello! I have table with employee status:
| Name | ID | Status | StartDate | EndDate |
| Jon Smith | 283 | Business trip | 2/10/2022 | 2/13/2022 |
| Jon Smith | 283 | Business trip | 4/20/2022 | 4/22/2022 |
| Jon Smith | 283 | Vacations | 7/31/2020 | 8/14/2020 |
| Jon Smith | 283 | Vacations | 6/7/2021 | 6/11/2021 |
| Jon Smith | 283 | Vacations | 8/16/2021 | 8/29/2021 |
| Jon Smith | 283 | Vacations | 11/10/2021 | 11/11/2021 |
| Jon Smith | 283 | Vacations | 11/15/2021 | 11/21/2021 |
| Jon Smith | 283 | Vacations | 4/11/2022 | 4/15/2022 |
| Jon Smith | 283 | Job | 2/3/2020 | |
| Melinda Yacobs | 123 | Vacations | 10/14/2019 | 10/14/2019 |
| Melinda Yacobs | 123 | Vacations | 11/14/2019 | 11/15/2019 |
| Melinda Yacobs | 123 | Vacations | 12/4/2019 | 12/6/2019 |
| Melinda Yacobs | 123 | Vacations | 3/16/2020 | 3/20/2020 |
| Melinda Yacobs | 123 | Vacations | 7/20/2020 | 8/2/2020 |
| Melinda Yacobs | 123 | Vacations | 8/6/2020 | 8/7/2020 |
| Melinda Yacobs | 123 | Vacations | 9/7/2020 | 9/13/2020 |
| Melinda Yacobs | 123 | Vacations | 11/12/2020 | 11/21/2020 |
| Melinda Yacobs | 123 | parental leave | 3/4/2021 | |
| Melinda Yacobs | 123 | Unpaid | 11/21/2019 | 11/21/2019 |
| Melinda Yacobs | 123 | Job | 8/1/2019 |
I want to get the latest status for today with the help measure. What is important for the second employee, this status is not a job, but a parental leave (because in the future she returns to work)
Hi vaalyushin ,
Sure. Here it is :
1) Load source data into power query2) Add a custom column to calculate all dates between start and end dates. In case end date is null, we replace it by current date. We need to convert dates to numbers in order to perform this calculation. This gives us a list of values as numbers for each row.
3) We then expand the list into new rows so we get one row for each date that lies between start and end date. We need to convert the numeric data type of the column into date. We also remove the start and end dates columns.
4) This gives us the final dataset. Column status date is the date column based on which we can compute the latest status.
5) In DAX, we create two measures, one to compute max date for each person and the other to compute the latest status.Max Status Date =CALCULATE(MAX('Job Status'[Status Date]),ALLEXCEPT('Job Status','Job Status'[Name]))Current Status =CALCULATE(CONCATENATEX(VALUES('Job Status'[Status]), 'Job Status'[Status], " , "),FILTER(ALLEXCEPT('Job Status','Job Status'[Name]),'Job Status'[Status Date] = [Max Status Date]))
This gives us the final resultHere is the M-code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZLBasMwEER/RfgcWGnlyPG1x0BPpYUSfFASQQWObGK3319J7toqLY58EbOwT4NGczoVx86xl5sdP4pdgQfpz6fPwTozDGy8297PggMCcsSgJelml8Mih5JYRNL/s2/6okfbucFrKaAKuzx4lnCY9GOuAhVWRcAE6ceYUJNF4LAmncFx8DaLIQ0Z5D4hcRMplkj9LauRHrtzCHP6s5Bl3Hs2rXVXzd71pTvHK/GPSQnh27mofw8bcDHj+3nIxD2NRKtZ53orkPRcXz+5VCcD9sDcO0xrl8Eq2g813IZWUM9tl6Rz34s/5eFpk1bxXt+NG3XLWqO/TAxcUhfXuFfXa3tliU/8omRYgacuiphMxFjRNN8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, ID = _t, Status = _t, StartDate = _t, EndDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"ID", Int64.Type}, {"Status", type text}, {"StartDate", type date}, {"EndDate", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [EndDate] <> null
then
{Number.From([StartDate])..Number.From([EndDate])}
else
{Number.From([StartDate])..Number.From(Date.From(DateTime.FixedLocalNow()))}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"StartDate", "EndDate"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Status Date"}})
in
#"Renamed Columns"Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊
3 Replies
rohit_singhSolution Sage
Hi vaalyushin ,
Sure. Here it is :
1) Load source data into power query2) Add a custom column to calculate all dates between start and end dates. In case end date is null, we replace it by current date. We need to convert dates to numbers in order to perform this calculation. This gives us a list of values as numbers for each row.
3) We then expand the list into new rows so we get one row for each date that lies between start and end date. We need to convert the numeric data type of the column into date. We also remove the start and end dates columns.
4) This gives us the final dataset. Column status date is the date column based on which we can compute the latest status.
5) In DAX, we create two measures, one to compute max date for each person and the other to compute the latest status.Max Status Date =CALCULATE(MAX('Job Status'[Status Date]),ALLEXCEPT('Job Status','Job Status'[Name]))Current Status =CALCULATE(CONCATENATEX(VALUES('Job Status'[Status]), 'Job Status'[Status], " , "),FILTER(ALLEXCEPT('Job Status','Job Status'[Name]),'Job Status'[Status Date] = [Max Status Date]))
This gives us the final resultHere is the M-code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZLBasMwEER/RfgcWGnlyPG1x0BPpYUSfFASQQWObGK3319J7toqLY58EbOwT4NGczoVx86xl5sdP4pdgQfpz6fPwTozDGy8297PggMCcsSgJelml8Mih5JYRNL/s2/6okfbucFrKaAKuzx4lnCY9GOuAhVWRcAE6ceYUJNF4LAmncFx8DaLIQ0Z5D4hcRMplkj9LauRHrtzCHP6s5Bl3Hs2rXVXzd71pTvHK/GPSQnh27mofw8bcDHj+3nIxD2NRKtZ53orkPRcXz+5VCcD9sDcO0xrl8Eq2g813IZWUM9tl6Rz34s/5eFpk1bxXt+NG3XLWqO/TAxcUhfXuFfXa3tliU/8omRYgacuiphMxFjRNN8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, ID = _t, Status = _t, StartDate = _t, EndDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"ID", Int64.Type}, {"Status", type text}, {"StartDate", type date}, {"EndDate", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [EndDate] <> null
then
{Number.From([StartDate])..Number.From([EndDate])}
else
{Number.From([StartDate])..Number.From(Date.From(DateTime.FixedLocalNow()))}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"StartDate", "EndDate"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Status Date"}})
in
#"Renamed Columns"Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊- rohit_singh
Hello vaalyushin ,
Based on my experience there is a really simple solution using PowerQuery. I'm not sure if you're looking to resolve this specifically using DAX, but I'll mention my results here anyway.
I have assumed null values in the End date column to be the current date, else calculations will generate errors.This is the final result aftre applying transformations in Power Query :
You will notice that there are overlaps in status for the same month, so for 2nd user it shows current status as job and parental leave, since null values for end date have been replaced by today's date.
If this solution using power query suits your requirements, please let me know and I'll be happy to explain the steps in detail.Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊
vaalyushincan you explain this solution?