Calculate with multiple tables

[avalerion]

I can't seem to figure out how to calculate between two tables. I have two dates in a single table, statement_date and date_created. If the two match, it's a new customer. If not, existing customer. There's a slicer on the report page using statement_date... but my formula does not return any values. Any tips?

Sales New Cust = CALCULATE(sum('account_payment'[statement_amount]),
filter(
all('account_payment'),
'account_payment'[date_created]=DATE(year('account_payment'[statement_date]),month('account_payment'[statement_date]),day('account_payment'[statement_date]))))

[Greg_Deckler]

Seems like you are unnecessarily complicating your formulas here. You could create a new column in your table like:

Column = IF([Date1] = [Date2],1,0)

1 is a new customer, 0 is not a new customer.

Sales New Cust = CALCULATE(sum('account_payment'[statement_amount])​,
filter(
all('account_payment'),
Column = 1)

I'd have to see your model to know if this is correct, you might need a RELATED in there, etc. Sample data and model would assist.

[avalerion]

That works as a standalone, but what suggestion do you have for a date column that's linked to a slicer on the same report tab?

[Greg_Deckler]

Can you provide some more details? I am having trouble visualizing your data and relationships. Is that date column you mention in a date table and are your tables related to one another?

[avalerion]

It's a single table- the columns I'm trying to work with inside of it are:

date_created (date client started)

statement_date (date of payment)

statement_amount (amount of payment)

I created the column that checks if date_created = statement_date and then show 1 or 0. I'm looking to sum the amount of payments for new clients, since payment is due to create the account.

[Greg_Deckler]

That sounds like a measure like:

Amount = SUMX(FILTER(tablename,tablename[isnew]=1),[statement_amount])

tablename is the name of your table

isnew is your column that is either 1 or 0

You can put that measure in a table with date_created and have the sums for each date or you could use a date slicer for date_created or statement_date and have the Amount in a card and you could see the amount as you select a date, or you could put date as the x-axis of a line graph and graph out the amount per date. What exactly are you looking to do?

[greggyb · Accepted solution]

@Greg_Deckler, the SUMX( FILTER() ) idiom you've suggested is very much a performance antipattern.

The preferred construction would be:

CALCULATE(
    SUM( 'Table'[statement_amount] )
    ,'Table'[isnew] = 1
)

The construction utilizing SUMX( FILTER() ) would have to iterate the fact table twice to achieve a result. First FILTER() would step through every row in the fact table performing the test against [isnew]. It doesn't matter that we know there are only two possible values of [isnew] to test, FILTER(), by design tests every row of its input table.

After the FILTER() returns, the resultant table (some significantly smaller subset of the fact table) would then be iterated again.

[Greg_Deckler]

Make sure to tell Microsoft since that comes directly from their SUMX example. 🙂

[fbrossard]

I agree with @greggyb SUMX + FILTER is not an optimized combo. Because you iterate over each lines. SUMX Vs CALCULATE is like CELL BY CELL COMPUTATION VS BLOCK COMPUTATION. 

Microsoft's samples are not what you should do 🙂