DAX - Filtering on dates across multiple dimensions using datediff

[Anonymous]

Hi,

New user here, so play nice with me!  I don't bite.

Firstly, I am using a live connection.

I am trying to build a new measure that grabs an amount field, based on 3 filters.  In words, I need to grab and sum amount1, if amount2 is greater than 0 and category = 'x' and amount_date < 31 days of data_effective_date.  Amount_date and data_effective_date are in two separate dimensions.

In my head, the query that I would like to create would look something like this:

Calc_Amount = CALCULATE(SUM(vw_fact_reporting_day[amount1]),
FILTER(vw_fact_reporting_day, vw_fact_reporting_day[amount2] > 0),
FILTER(vw_category, vw_category[category] = "x" ),
FILTER(vw_date_amount, DATEDIFF(vw_date_amount[amount_date],vw_date_effective[data_effective_date],DAY) > 31))

As we know, we cannot use multiple tables within the same filter, so the above doesn't work.

I would ideally like to keep this in DAX, as I need to create two measures that I can then do a calculation on.  Also note that:

vw_date_effective[data_effective_date]

is set as a page level filter.

I am at a loss to work out how I might acheive this.  Is anyone able to give me any indication or point me in the right direction?

Thanks

[edhans]

As we know, we cannot use multiple tables within the same filter, so the above doesn't work.

---

Why not? You cannot use multiple tables within the same FILTER() function, but you can use multiple FILTER functions within CALCULATE. I just wrote this real quickly using one of MS's sample databases and it worked fine.

zzzTemp = 
CALCULATE(
    Sales[Sales Amount],
    FILTER('Product','Product'[ABC Class] = "A"),
    FILTER(Customer,Customer[Birth Date] < DATE(1980,1,1)),
    FILTER(Promotion,Promotion[Promotion]<>"European Spring Promotion")
)

Note that all of those tables are related to each other in the Model tab. 

[Anonymous]

Thanks for replying.

The thing is, though, you have hard coded a date into the query.  I want to be able to do a datediff between two dates from separate tables and not hard code them.

Can I nest filters inside filters?

[edhans]

I hardcoded a date because I was testing mutltiple filters and not doing date logic. I don't even have a Dates table in this model yet.

You don't need to nest FILTER functions. Just do a datediff(). DateDIff needs two scalar values, a start date and an end date, so you might need to use a FILTER to get your desired date, then wrap it in MAX() to convert to a scalar value. 

Do your functions one at a time. Your ending formula will be pretty big. I'd be inclined to do some of this in Power Query, both for simplicity and because this will often fold and let the server do the work for you.