Calculate function with 2 filters

[RMV]

Hi,

I'm trying to calculate an amount with 2 filters for sales amount with Type = Inflow and which falls in previous month.

I have this formula, but it resulted blank.

CALCULATE(SUM(Entry_all[Amount]),FILTER(Entry_all,Entry_all[Type]="Inflow"),DATEADD('Date'[Date],-1,MONTH))

Can anyone advise what did I do wrong & advise the correct one?

Thanks

[OwenAuger · Accepted solution]

Hi @RMV

Try this:

=
CALCULATE (
    SUM ( Entry_all[Amount] ),
    Entry_all[Type] = "Inflow",
    DATEADD ( 'Date'[Date], -1, MONTH )
)

The problem with the original formula is due to how table filter arguments provided to CALCULATE are treated.

As a rule of thumb, if you want to filter a particular column using CALCULATE, you should provide a filter argument referring to that column only, not an entire table.

If you provide a table filter argument, and that table is a physical table of your data model (potentially filtered), then in fact the "expanded" version of the table is applied as a filter argument. This means a filter consisting of all columns of the expanded table is created.

The expanded version of Entry_all includes any columns of tables reachable by tracing many:1 relationships starting from Entry_all, which includes the 'Date' table. This means the expanded version of Entry_all includes columns corresponding to dates in the current filter context, which, if you are filtering on a single month, will not intersect with the dates returned by DATEADD, which are one month earlier.

(As it happens, dates from the "current month" are present in the original columns of Entry_all anyway regardless of the Date table.)

 Instead, for your first filter argument you really just want to apply a filter to the Entry_all[Type] column.

There are various articles on this subject.

A post I see referred to a lot related to this topic is The Logic behind the Magic of DAX Cross Table Filtering by Jeffrey Wang.

Regards,

Owen

[RMV]

Hi @OwenAuger,

It's really a comprehensive advise & help. Thanks a lot!

I'm just started to learn dax, and most of the times, learning by doing the cases.

To have a full explanation including what's wrong with my previous formula, it really a great help. Thanks again!

Regards,