Comparision of person ID based on current week with previous week

[aditya369]

Hi i need help,

i need to create calculated column with dax where i need to compare employee id based on current week with previous week and result should be like below:

1."Added" — was in current week, not in last week
2."Carry Forward" — present in both weeks

[v-karpurapud]

Hi @aditya369 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

Thank you.

[v-karpurapud]

Hi @aditya369 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @aditya369 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud]

Hi @aditya369 

Thank you for reaching out to the Microsoft Fabric Community Forum.

Based on your requirement to create a calculated column that compares employees between the current week and the previous week, and then classifies them as either "Added" or "Carry Forward", please find the solution below.

DAX


Status = 
VAR CurrentEmp = EmployeeWeeklyPresence[EmployeeID]
VAR CurrentWeek = EmployeeWeeklyPresence[WeekBeginning]
VAR PreviousWeek = CurrentWeek - 7

VAR WasInPreviousWeek =
    CALCULATE(
        COUNTROWS(EmployeeWeeklyPresence),
        FILTER(
            EmployeeWeeklyPresence,
            EmployeeWeeklyPresence[EmployeeID] = CurrentEmp &&
            EmployeeWeeklyPresence[WeekBeginning] = PreviousWeek
        )
    )

RETURN 
    IF(WasInPreviousWeek > 0, "Carry Forward", "Added")

Please find the below Snapshot for reference:

If this solution meets your expectations, kindly mark it as Accepted Solution to help others with similar
queries.

If you have additional conditions or a more complex model, please share more detailed information, and we will be happy to assist you further.

Thank you!

Microsoft Fabric Community Support

[aditya369]

Hi,

thanks for your quick response. please consider below table for reference.

Week beginning EMPID   status
03-Feb-25     100   carried forward
10-Feb-25     100   Moved out this week
24-Feb-25     100  Added this week
03-Mar-25     100  Carried forward

Logic 1: Any Employee id with starting date the status should be "Carried forward"

Logic 2: Any Employee id exist in current week and doesnot exist in next week the status should be"Moved out" (in above table 17-Feb-25 Employee id doesnot exist)
Logic 3: Any Employee id exist in both previous week and current week then the status should be "Carried forward"
Logic 4: Any Employee id does not exist in previous but exist in current week then the status should be "Added this week"

Please help me with this exact scenario

[v-karpurapud]

Hi @aditya369 

Please try the below DAX:

Status = 
VAR CurrentEmp = EmployeeWeeklyPresence[EmployeeID]
VAR CurrentWeek = EmployeeWeeklyPresence[WeekBeginning]
VAR PreviousWeek = CurrentWeek - 7
VAR NextWeek = CurrentWeek + 7

---Check if present in previous week
VAR WasInPreviousWeek =
    CALCULATE(
        COUNTROWS(EmployeeWeeklyPresence),
        FILTER(
            EmployeeWeeklyPresence,
            EmployeeWeeklyPresence[EmployeeID] = CurrentEmp &&
            EmployeeWeeklyPresence[WeekBeginning] = PreviousWeek
        )
    )

-- Check if present in next week
VAR IsInNextWeek =
    CALCULATE(
        COUNTROWS(EmployeeWeeklyPresence),
        FILTER(
            EmployeeWeeklyPresence,
            EmployeeWeeklyPresence[EmployeeID] = CurrentEmp &&
            EmployeeWeeklyPresence[WeekBeginning] = NextWeek
        )
    )

-- Check if this is the first appearance of the employee
VAR IsFirstWeekForEmp =
    CALCULATE(
        MIN(EmployeeWeeklyPresence[WeekBeginning]),
        FILTER(
            EmployeeWeeklyPresence,
            EmployeeWeeklyPresence[EmployeeID] = CurrentEmp
        )
    ) = CurrentWeek

RETURN
    SWITCH(
        TRUE(),
        IsFirstWeekForEmp, "Carried forward",
        WasInPreviousWeek = 1 && IsInNextWeek = 0, "Moved out this week",
        WasInPreviousWeek = 1, "Carried forward",
        WasInPreviousWeek = 0, "Added this week"
    )

If you find this post helpful, kindly mark it as Accepted Solution.

Thank You.

[aditya369]

Hi,

Thanks for your response, on mar 3 date its showing moved out but those are carry forward. please check.

[aditya369]

Hi,

thanks for the response, only on one date values are getting wrong i.e on max date. 

on 3r mar 2025, both 100 and 200 existed from previous week so it should appear as "carried forward". please work on this its my request.

[v-karpurapud]

Hi @aditya369 

I believe you may have overlooked the screenshot I provided. Could you please review it, as it meets the requirements you requested on 3rd March 25 it appearing as carried forward for both 100 and 200?

If this post helps, kindly mark it as Accepted Solution.

Thank You!

[aditya369]

In this solution 17 feb for ID 300 should be moved out because its not there in next week.

[v-karpurapud · Accepted solution]

Hi @aditya369 

I would like to clarify that I have taken sample data which includes a few dates. When it comes to ID 300, it is present on 10-Feb and also on 17-Feb. After that, there are only a few dates which include ID 100 and ID 200. If you want the exact solution including all the dates, kindly share the sample data so that we can assist you better.

Thank You!

[aditya369]

Hi,

thanks for your quick response, only one logic is not working in above i.e for ID: 100 is existing in current week and previous week i.e on 03-mar-2025 and 24-Feb-2025 so on 03-mar-2025 it should be carry forward for empid 100 & 200. because it exists in current week and previous week. please help me with this.

[v-karpurapud]

Hi @aditya369 

Try the below DAX:

Status = 
VAR CurrentEmp = EmployeeWeeklyPresence[EmployeeID]
VAR CurrentWeek = EmployeeWeeklyPresence[WeekBeginning]
VAR PreviousWeek = CurrentWeek - 7
VAR NextWeek = CurrentWeek + 7

VAR WasInPreviousWeek =
    CALCULATE(
        COUNTROWS(EmployeeWeeklyPresence),
        FILTER(
            EmployeeWeeklyPresence,
            EmployeeWeeklyPresence[EmployeeID] = CurrentEmp &&
            EmployeeWeeklyPresence[WeekBeginning] = PreviousWeek
        )
    )


VAR IsInCurrentWeek =
    CALCULATE(
        COUNTROWS(EmployeeWeeklyPresence),
        FILTER(
            EmployeeWeeklyPresence,
            EmployeeWeeklyPresence[EmployeeID] = CurrentEmp &&
            EmployeeWeeklyPresence[WeekBeginning] = CurrentWeek
        )
    )


VAR IsInNextWeek =
    CALCULATE(
        COUNTROWS(EmployeeWeeklyPresence),
        FILTER(
            EmployeeWeeklyPresence,
            EmployeeWeeklyPresence[EmployeeID] = CurrentEmp &&
            EmployeeWeeklyPresence[WeekBeginning] = NextWeek
        )
    )


VAR IsFirstWeekForEmp =
    CALCULATE(
        MIN(EmployeeWeeklyPresence[WeekBeginning]),
        FILTER(
            EmployeeWeeklyPresence,
            EmployeeWeeklyPresence[EmployeeID] = CurrentEmp
        )
    ) = CurrentWeek

RETURN
    SWITCH(
        TRUE(),
         Current + Previous week = Carry Forward
        WasInPreviousWeek > 0 && IsInCurrentWeek > 0, "Carried forward",
        
        IsFirstWeekForEmp, "Carried forward",

        WasInPreviousWeek > 0 && IsInCurrentWeek = 0, "Moved out this week",

        WasInPreviousWeek = 0 && IsInCurrentWeek > 0, "Added this week"
    )

If you find this post helpful, kindly mark it as Accepted Solution.

Thank You!
 

[DataNinja777]

Hi @aditya369 ,

To calculate employee movement between weeks using a disconnected calendar table and a simple employee table with Hire date and Leaving date, the most effective approach is to use DAX measures instead of calculated columns. The Employee Change table contains EmployeeID, Hire date, and Leaving date, and the Calendar table is disconnected and used to slice the data by week.

The base headcount measure checks whether the selected date is within the employee’s active period. The formula is:

Headcount =
SUMX (
    'Employee Change',
    IF (
        MAX ( 'Calendar'[Date] ) >= 'Employee Change'[Hire date] &&
        MAX ( 'Calendar'[Date] ) <  'Employee Change'[Leaving date],
        1
    )
)

To compare with the previous week, subtract 7 days from the selected date:

Previous Week Headcount =
SUMX (
    'Employee Change',
    IF (
        MAX ( 'Calendar'[Date] ) - 7 >= 'Employee Change'[Hire date] &&
        MAX ( 'Calendar'[Date] ) - 7 <  'Employee Change'[Leaving date],
        1
    )
)

An employee is considered “Added” if they are in the current week but were not present the previous week:

Added =
SUMX (
    'Employee Change',
    IF (
        MAX ( 'Calendar'[Date] ) >= 'Employee Change'[Hire date] &&
        MAX ( 'Calendar'[Date] ) <  'Employee Change'[Leaving date] &&
        NOT (
            MAX ( 'Calendar'[Date] ) - 7 >= 'Employee Change'[Hire date] &&
            MAX ( 'Calendar'[Date] ) - 7 <  'Employee Change'[Leaving date]
        ),
        1
    )
)

An employee is classified as “Moved Out” if they were present in the previous week but not in the current week:

Moved Out =
SUMX (
    'Employee Change',
    IF (
        MAX ( 'Calendar'[Date] ) - 7 >= 'Employee Change'[Hire date] &&
        MAX ( 'Calendar'[Date] ) - 7 <  'Employee Change'[Leaving date] &&
        NOT (
            MAX ( 'Calendar'[Date] ) >= 'Employee Change'[Hire date] &&
            MAX ( 'Calendar'[Date] ) <  'Employee Change'[Leaving date]
        ),
        1
    )
)

An employee is considered “Carry Forward” if they are present in both current and previous weeks:

Carry Forward =
SUMX (
    'Employee Change',
    IF (
        MAX ( 'Calendar'[Date] ) >= 'Employee Change'[Hire date] &&
        MAX ( 'Calendar'[Date] ) <  'Employee Change'[Leaving date] &&
        MAX ( 'Calendar'[Date] ) - 7 >= 'Employee Change'[Hire date] &&
        MAX ( 'Calendar'[Date] ) - 7 <  'Employee Change'[Leaving date],
        1
    )
)

These measures can be used in a table visual where the Calendar[Date] slicer filters a specific week (e.g. Monday of each week), showing the count of employees who were added, moved out, or carried forward from the previous week. This approach is efficient, flexible, and follows best practices for headcount tracking in Power BI.

Best regards,

[aditya369]

Hi Ninja,

Thanks for the quick response but here we need calculated column to use that in slicer aswell.

note: we have week beginning column in date aswell. please try again that would help for me