Creating a variance column within datatable to compare movement in value between dates

[AaronO23]

Hello,

I am trying to create a calculated column to show the change in a value between dates. The following is a simplified sample data for what I am working with:

Month	Pers.No	Full Name	Cap.Util.% (SAP)	FTE
Feb-24	1	Employee 1	100	1
Feb-24	2	Employee 2	100	1
Feb-24	3	Employee 3	100	1
Jan-24	1	Employee 1	100	0
Jan-24	2	Employee 2	100	1
Jan-24	3	Employee 3	100	1
Mar-24	1	Employee 1	100	1
Mar-24	2	Employee 2	100	1
Mar-24	3	Employee 3	100	1
Mar-24	4	Employee 4	100	1
Apr-24	1	Employee 1	100	1
Apr-24	2	Employee 2	100	0
Apr-24	3	Employee 3	100	0
Apr-24	4	Employee 4	100	1

I would like to add a column that shows the movement in FTE from prior month for each employee. For example, the first row (Employee 1, Feb-24) would show a 1 due to the FTE for Employee 1 being 0 in January; Employee 4 would be a 1 in March; and Employee 2/3 a -1 in April. 

I have tried this DAX code but am running into error messages on the VAR PreviousMonth line. 

Variance =

VAR CurrentMonth = 'Table'[Month]

VAR PreviousMonth = Edate(CurrentMonth, -1)

VAR CurrentFTE = 'Table'[FTE]

VAR PreviousFTE = CALCULATE(SUM('Table'[FTE]), 'Table'[Month] = PreviousMonth)

RETURN

    CurrentFTE - PreviousFTE

Is this a simple fix I am missing, or am I just using the wrong solution altogether?

Thank you

[Anonymous]

Hi,@AaronO23 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. Below are the Calculated Columns I've created for your needs:

Column = 
VAR CURRRENTMONTH =MONTH([Month])
VAR PREVIOUSMONTH1=IF(CURRRENTMONTH=1,12,CURRRENTMONTH-1)
VAR PRE=CALCULATE(SUM('Table'[FTE]),FILTER(ALL('Table'),MONTH('Table'[Month])=PREVIOUSMONTH1&&'Table'[Full Name]=EARLIER('Table'[Full Name])))
RETURN [FTE]-PRE

3.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AaronO23]

Hello, thank you for this. This has helped somewhat but I have realised an issue with my full dataset which means an extra step would be needed. The issue is that if an employee leaves in March (for example), they will not be in the April listing at all. Therefore, the above solution helps identify those that join the company, or reduce their FTE, but not those that leave (as they will not be in the list for a -1 to appear in the new column).

Is this something that could be fixed with an addition to the above solution, or do I need to relook at my dataset?

Thank you

[Anonymous · Accepted solution]

HI,@AaronO23 

Regarding the issue you raised, my solution is as follows:

Depending on your needs, I suggest that if there is a problem with the data itself, directly modifying the data source is the most direct and effective approach. This ensures the accuracy and consistency of the data and avoids errors in reporting or analysis. Using DAX, while it is possible to adjust data through calculations, is used more for data analysis and generating insights than for data cleaning or data correction. This facilitates long-term data management and maintenance.

Of course, if you have a new column indicating the current employee status, here is my solution:

1.First I have created the following table and the column names and data are the data you have given:

2. Below are the calculation column  I've created for your needs:

FLAG = 
VAR USERN='Table (2)'[Full Name]
VAR CUREM=MONTH('Table (2)'[Month])
VAR FSTATUE=
CALCULATE(MAX('Table (2)'[statue]),FILTER('Table (2)','Table (2)'[Full Name]=USERN&&'Table (2)'[statue]="F"))
RETURN IF(FSTATUE="F"&&'Table (2)'[statue]<>"F",1,0)

3.Create a new table:

Table 2 = FILTER('Table (2)','Table (2)'[FLAG]=0)

4.Here's my final result, which I hope meets your requirements.

Then use the latest table and use the method in my last post to meet your needs.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.