Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Switching between row and table context in DAX/Calculating 12-month staff attrition measure

So I'm trying to create a measure that returns the 12-month staff attrition for teams within an organization, i.e. what percentage of staff leave over a 12 month period.   My sources are lists of s...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous ,

     

    Suppose [Actual Month (of List)] is set to Date type.

     

    Please refer to below formulas to create calculated columns.

    my + 12 =
LOOKUPVALUE (
    staff[Actual Month (of List)],
    staff[Employee ID], staff[Employee ID],
    staff[Actual Month (of List)].[Month], staff[Actual Month (of List)].[Month],
    staff[Actual Month (of List)].[Year], staff[Actual Month (of List)].[Year] + 1
)

No Change/Left =
IF ( staff[my + 12] <> BLANK (), "No change", "Left" )

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Anonymous ,

 

Suppose [Actual Month (of List)] is set to Date type.

 

Please refer to below formulas to create calculated columns.

my + 12 =
LOOKUPVALUE (
    staff[Actual Month (of List)],
    staff[Employee ID], staff[Employee ID],
    staff[Actual Month (of List)].[Month], staff[Actual Month (of List)].[Month],
    staff[Actual Month (of List)].[Year], staff[Actual Month (of List)].[Year] + 1
)

No Change/Left =
IF ( staff[my + 12] <> BLANK (), "No change", "Left" )

 

Best regards,

Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi v-yulgu-msft,

 

Thanks, that works great! I didn't know you could use the same field as the search column name and value!