CALCULATE / FILTER DAX code issue

[SJHardeman]

Hi, I have been trying to use the below DAX code to do the following:

(1) take the Data table, sum the FTE column filtered whereby the date in the Period column equals a measure [Latest month]

(2) take the Data table, sum the FTE column filtered whereby the date in the Period column equals a measure [First month]

(3) subtract (2) from (1) to get the FTE_change

For some reason this isn't working, for each of (1) and (2) the result is the Sum of all FTE in Data, i.e. the Filter parts of the formulae are not working, and therefore (3) = (2) - (1) = 0. 

I can't share my model or underlying data, but does anyone have any ideas what may be causing the issue here?

FTE_change = CALCULATE(

    SUM(Data[FTE]),

    FILTER(Data, [Latest month]='Data'[Period])) - CALCULATE(

    SUM(Data[FTE]),

    FILTER(Data, [First month]='Data'[Period]))

For info, the [Latest month] measure = LASTDATE(Data[Period]) and

the [First month] measure = FIRSTDATE(Data[Period])

[OwenAuger]

Hi again @SJHardeman 

This should work:

FTE Penultimate Month =
CALCULATE (
    [FTE Sum],
    PREVIOUSMONTH (
        LASTDATE ( Staff_Collated_HQ_WFPT[Period] )
    )
)

Notes:

• EDATE returns a scalar value (not a table) so isn't suitable to be used directly as a SetFilter argument of CALCULATE.
• PREVIOUSMONTH instead returns a table containing all dates of the month preceding the minimum date in the argument that it is passed.
• In this case, this will be the month preceding the maximum Period date in the filter context which is returned by LASTDATE (as a 1x1 table).
• DATEADD ( LASTDATE ( ... ), -1, MONTH could also be an option here, but as you are not using a Date table at the moment, this would only work reliably if the Period dates are aligned in their position within the month (e.g. always first or last day, or nth day of the month).

As a general comment, I would highly recommend creating a Date/Calendar dimension table and using it for all date filtering.

(See this article for example https://www.daxpatterns.com/standard-time-related-calculations/)

This is because time intelligence functions such as LASTDATE and PREVIOUSMONTH base their logic on dates that exist in the date column provided (in your case the Period column). With more complex date filters, things can go awry if you don't have a Date table with contiguous dates and complete months that is marked as a date table.

Regards

[OwenAuger · Accepted solution]

Hi @SJHardeman 

First, here's a bit of an explanation of what's going on:

1. When a measure such as [Latest month] is invoked, it is automatically surrounded by a hidden CALCULATE (...). 
2. The FILTER function is an iterator which evaluates the condition in the 2nd argument in the row context of each row the table specified in the 1st argument.
3. Within a row context, CALCULATE triggers context transition, which transforms the row context into an equivalent filter context. All columns within the "current row" become filters.
4. Applying this to your measure: [Latest month] and [First month] are evaluated within a filter context corresponding to each row of the Data table. This means the conditions [Latest month]='Data'[Period] and [First month] = 'Data'[Period] are always TRUE.

Here is a good article on this overall topic:

https://www.sqlbi.com/articles/understanding-context-transition-in-dax/

A couple of side points:

1. FIRSTDATE and LASTDATE return tables (1 row x 1 column), and are typically used as SetFilter arguments within CALCULATE. If you just want to return a scalar value, you can use MIN or MAX.
2. For any date-based filtering, it is best to create a separate 'Date' dimension. However, I won't worry about this for the purpose of this question.

You can fix this a few ways. I would personally recommend creating these measures:

FTE Sum = 
SUM ( data[FTE] )

FTE First Month = 
CALCULATE(
    [FTE Sum],
    FIRSTDATE ( data[Period] )
)

FTE Last Month = 
CALCULATE(
    [FTE Sum],
    LASTDATE ( data[Period] )
)

FTE_change = 
[FTE Last Month] - [FTE First Month]

Does this work for you?

Regards

[SJHardeman]

Hi Owen, thanks for explaining this so clearly. The solution worked. Thanks, Sam

[SJHardeman]

Hi @OwenAuger, how would I modify the code if I wanted to also have another measure which calculated FTE_change for [FTE Last Month] - [FTE Penultimate Month]. Using the below

results in an error

FTE Penultimate Month = CALCULATE([FTE Sum], EDATE(LASTDATE(Staff_Collated_HQ_WFPT[Period]),-1))