Previous Transaction date DAX help

[SamTailor]

Please help me,

how can I get Previous Transaction date in DAX?

The data set is not that simple its is not sorted, has multimple customers with multile products, and resellers. The goal is to get previous transaction date for the product of this client and this reseller

[walid_barakeh]

Im trying to do the same, but the difference that Im applying the filters from the dims (dimsite, dimdept) 
and Im always applying filter on dimdate relative 1 day back. how I could find the previous working day per site,dept ?
I need in a measure so I could use this date in other measures .
my table called factcycletable which includes date,siteid,deptid and more ....
the solution here  didnt help me so much 

[v-huizhn-msft]

Hi @SamTailor,

Have you resolved your issue? If you have, please mark the right reply as answer. So more people will find the workaround easily.

Best Regards,
Angelia

[SamTailor]

The idea with VARs worked only for test data, once i tried to do it with 300 000 rows and 15 columns power BI crashed every ime i tried to do it, nnot enough memory(

[Tak94]

I have the same problem. The VAR method does not work when working with large amounts of data.

Is there another way?

[v-huizhn-msft]

Hi @SamTailor,

Right click the table->Query Edit, please add an index column, please see the button highlighted in yellow background.



Then  you can create a calculated column to get the previous the transaction date based on the index column.

Previous transaction date=LOOKUPVALUE(Table[Transaction date],Table[index],Table[index]-1)

Best Regards,
Angelia

[TomMartens · Accepted solution]

Hey,

here is an example for a calculated column using variable instead of the EARLIER function

Previous transaction date = 
var currentDate = 'yourTableName[Transaction date]
var currentCustomer = 'yourTableName'[Client]
var currentReseller = 'yourTableName'[Reseller]
var currentProduct = 'yourTableName'[Product]
return
CALCULATE(MAX('yourTableName'[Transaction date]),
    FILTER(ALL('yourTableName'),
        'yourTableName[Date] < currentDate
        && 'FactWithDates'[Customer] = currentCustomer
	&& 'yourTableName'[Reseller] = currentReseller
	&& 'yourTableName'[Product] = currentProduct
    )
)

• For each row the current values for Client, Reseller, and Product are stored in variables.
• CALCULATE is used to transform the existing ROWCONTEXT (we are creating a calculated column), into a FILTER CONTEXT
• The now existing FILTER CONTEXT  has to be expanded, to gain access to all records using FILTER(ALL('yourTableName), ...
• The rows are filter down using the variables
• The MAX transaction date is calculated, from all Transactions date that are smaller than the date of the variable

Hope this helps 

[CahabaData]

EARLIER function

here is 1 example out there: http://tinylizard.com/dax-earlier-function/