# Forum, Tips & Tricks

Learn tips and tricks and engage in discussions with our blog authors here. It’s all for your benefit and learning.

# Value at the end of the month #DAX #LASTDATE #FILTER

Quote from Jan Havelka on 21. 6. 2019, 15:50Just in case any one of y'all ever come across a similar problem with counting number of e.g. employees at the end of the month, you might want to consider this solution.

The idea behind this is that we create a measure where we have a table of employees contracted from day X to day Y and we'd like to plot their count into a monthly graph - see the picture with illustrative data.

Picture 1: measure counting number of employees at the end of each monthThe issue that occurs is when an employee leaves in the middle of the month, at that point a simple

DISTINCTCOUNTisn't accurate anymore since there were more employees in the middle of the month than at the end. Therefore we can use the DAX functionCALCULATEwith an applied filter.However, there is a difference between using

CALCULATE(fx; filter)and

CALCULATE(

fx; FILTER(column = filter))With the first one we can use the function

LASTDATEand the measure then compares if the date matches the last day of the month. With the second one, the filter function adds an additional layer and theLASTDATEis then compared pretty much against each other, making it redundant.

Picture 2: difference between having FILTER in CALCULATE and how it evaluates the filter criteriaAnother workaround that can be useful when comparing the lastdate with some other days (since the if statement cannot be put into the filter function) is to create a column where the last day of the month is denoted and that can be again used into the filter that evaluates the criteria "row by row".

Just in case any one of y'all ever come across a similar problem with counting number of e.g. employees at the end of the month, you might want to consider this solution.

The idea behind this is that we create a measure where we have a table of employees contracted from day X to day Y and we'd like to plot their count into a monthly graph - see the picture with illustrative data.

*Picture 1: measure counting number of employees at the end of each month*

The issue that occurs is when an employee leaves in the middle of the month, at that point a simple *DISTINCTCOUNT *isn't accurate anymore since there were more employees in the middle of the month than at the end. Therefore we can use the DAX function *CALCULATE* with an applied filter.

However, there is a difference between using

CALCULATE(fx; filter)

and

CALCULATE(

fx; FILTER(column = filter))

With the first one we can use the function *LASTDATE* and the measure then compares if the date matches the last day of the month. With the second one, the filter function adds an additional layer and the *LASTDATE *is then compared pretty much against each other, making it redundant.

*Picture 2: difference between having FILTER in CALCULATE and how it evaluates the filter criteria*

Another workaround that can be useful when comparing the lastdate with some other days (since the if statement cannot be put into the filter function) is to create a column where the last day of the month is denoted and that can be again used into the filter that evaluates the criteria "row by row".