Identifying Status Change Between 2 Date Selections

[asa70]

Hi all,

I hope you are all well,

I have a task to showcase mobility of certain individuals between 2 dates. These dates are from 2 date slicers. Basically, when the user selects a start and end date. They need to see whether the individual has either moved or stayed between those date selections.
This is the desired output on the "Test" column below:

If the date slection was between these two:

 

I have tried using the following DAX measure, but I seem to not get the correct Test outcomes:

Test =
VAR _maxdate = calculate(max(Table[date]),allselected(Table))
VAR _prevstatus = calculate(
                              lastnonblank(Table[status],
                              _maxdate),
                             allexcept(Table,Table[Emp]))

VAR _currentstatus = selectedvalue(Table[status])
RETURN
if( _currentstatus = _prevstatus, 0, 1)

This the current result:

Any help would be appreciated.

Regards,

Asa

[bhanu_gautam]

@asa70 Create two measures to capture the department status at the start and end dates.
Compare these statuses to determine if the individual has moved or stayed.

StartDeptStatus =
CALCULATE(
FIRSTNONBLANK(Table[Dept], 1),
FILTER(
Table,
Table[Date] = MINX(ALLSELECTED(Table), Table[Date])
)
)

EndDeptStatus =
CALCULATE(
FIRSTNONBLANK(Table[Dept], 1),
FILTER(
Table,
Table[Date] = MAXX(ALLSELECTED(Table), Table[Date])
)
)

Test =
IF(
[StartDeptStatus] = [EndDeptStatus],
"Here",
"Moved"
)

[asa70 · Accepted solution]

Hi @bhanu_gautam , thank you so much for your solution. I did give it a try and it didn't give the correct answer at first. For the startdeptstatus measure, if included in table with all employees, it gave only one value for everything. I added a step to isolate the employees:

VAR _mindate = calculate(min(table[date]),allselected(table))
VAR _start = calculate(max(table[status]),
                                  table[date] = _mindate,
                                  table[emp] = max(table[emp]),
                                 allselected(table))
VAR _end = calculate(max(table[status]),
                                  table[date] = max(table[date]),
                                  table[emp] = max(table[emp]))

RETURN
if( _start = _end, "here", "moved")

Regards,
Asa

[v-echaithra]

Hi @asa70 ,

Thanks for your response,
If you have found the resolution, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,
Chaithra E