Hep with a powerpivot query

[shiur]

Hi am trying to write a dax query to create a calculated column to find the last date a transaction occurred.
My business logic is that for to select the max date for each customer create over a list of months where we have a positive transactions from that list (sometimes the customer does a returns/chargeback and that offsets the transactions they do that month)

(the data is being outputted into a table that has fields of: Customer Num & YYMM )


The Cash Transactions table has the following fields:
Customer Num,
Effective Date,
Payment Amount
YYMM (this is an internal field to track each month and year combo)

my issue is that my formula has a nested calculate statement on how I get the last transactions.

Here is the query I wrote...


=CALCULATE(MAX('Cash Tansactions'[Effective Date]),
FILTER('Cash Tansactions',EARLIER([Customer Num])='Cash Tansactions'[Customer Num]&&EARLIER([YYMM])='Cash Tansactions'[YYMM] ),
CALCULATE(sum('Cash Tansactions'[Payment Amount])>0,FILTER('Cash Tansactions',EARLIER([Customer Num])='Cash Tansactions'[Customer Num]&&EARLIER([YYMM])='Cash Tansactions'[YYMM]) )
)

 

[Matt Allington]

A couple of comments

1. This is not a DAX query, it is a DAX formula. DAX queries are completely different
2. If your objective is to find out the last date a customer purchased so you can use this info in a pivot table, you should not write a calc column but instead write a measure.
3. The YYMM column should be moved from your transactions table to a calendar table. Join the transactions to the calendar on the date column.
4. A simple measure something like this should work. It may need tweaking based on your data model and naming convention

last trans date = calculate(lastdate('cash transactions'[effective date]),'cash transactions[payment amount] > 0)

put cust number on rows in a pivot and then add the measure.

 

[shiur]

Thanks for the quick reply
1. thanks for the terminology
2 I cant use a Pivot Table I need the data to do more calculations
each month must be
3. your correct but this is quicker
4. each month needs to be summed together before you can calculate the sum amount (ie I want to see if the month is positive before I do the lookup for the month)


Thanks again