cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Measure 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)

1 ACCEPTED SOLUTION
Solution Sage

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}}),
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

3 REPLIES 3
Solution Sage

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}}),
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

Solution Sage

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

Helper I

can you explain this solution?

Announcements