Forum, Tips & Tricks

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

You need to log in to create posts and topics.

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

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".