Forum Discussion

asa70's avatar
asa70
Advocate I
1 year ago
Solved

Identifying Status Change Between 2 Date Selections

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

Need Help
  • asa70's avatar
    asa70
    1 year ago

    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

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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's avatar
      asa70
      Advocate I
      1 year ago

      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's avatar
        v-echaithra
        Community Support
        1 year ago

        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