Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 ) )
Hope this helps
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
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
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(
I have the same problem. The VAR method does not work when working with large amounts of data.
Is there another way?
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
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 ) )
Hope this helps
EARLIER function
here is 1 example out there: http://tinylizard.com/dax-earlier-function/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |