Forum Discussion
Creating a variance column within datatable to compare movement in value between dates
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.
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
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.