Forum Discussion

SJHardeman's avatar
SJHardeman
Frequent Visitor
3 years ago
Solved

CALCULATE / FILTER DAX code issue

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] ...
  • OwenAuger's avatar
    OwenAuger
    3 years ago

    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

OwenAuger's avatar
OwenAuger
Super User
3 years ago

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