Forum Discussion

kalpanaV's avatar
kalpanaV
Helper IV
8 years ago
Solved

Need Help

In this, i want the change in team in repsective month 

in which month he has changed team and want the count how many has changed,.

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi kalpanaV,

     

    Please modify the calculated column 'list' to below:

    List = CALCULATE(CONCATENATEX(VALUES(Sheet7[Team Name]),[Team Name],","),FILTER(ALL(Sheet7),Sheet7[Employee_id]=EARLIER(Sheet7[Employee_id])))

     

     

    Regards,

    Xiaoxin Sheng

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi kalpanaV,

     

    You can try to use below formula if it suitable for your requirement.

    1. Add date column as index to table.

    Date = DATEVALUE([Month]&" "&1)

     

    2. Add calculated column to calculate the last changed month.

    LastChange = 
var previous_Date=MAXX(FILTER(Sheet7,[Date]<EARLIER(Sheet7[Date])&&[Employee_id]=EARLIER(Sheet7[Employee_id])),[Date])
Return
IF(previous_Date<>BLANK()&&[Team Name]<>LOOKUPVALUE(Sheet7[Team Name],Sheet7[Employee_id],[Employee_id],Sheet7[Date],previous_Date),[Month],BLANK())

     

     

    3. Write measurs to calculate the change count.

    Change Count = CALCULATE(COUNT(Sheet7[LastChange]),Sheet7[LastChange]<>BLANK())

     

     

    Regards,

    Xiaoxin Sheng

    • kalpanaV's avatar
      kalpanaV
      Helper IV
      8 years ago

      I tried it, it s giving wrong values.

      Else, can we get  like this

       

       

      100 -Monitoring, Networking, Montioring

      101-  Montioring.DBA, Monitoring

       

      Just i want to list it i table. is this possible/.?

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi kalpanaV,

         

        >>I tried it, it s giving wrong values.

        Can you provide the detailed error message?

         

        >>Just i want to list it i table. is this possible/.?

        Yes, it is possible. You can create a calculate column with below formula:

        List = CONCATENATEX(FILTER(ALL(Sheet7),Sheet7[Employee_id]=EARLIER(Sheet7[Employee_id])),[Team Name],",")

         

         

        Regards,

        Xiaoxin Sheng