Measure for get current status employee

[vaalyushin]

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)

[rohit_singh · Accepted solution]

Hi @vaalyushin ,

Sure. Here it is :

1) Load source data into power query

2) 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 result

Here 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! 😊

[vaalyushin]

can you explain this solution?