Forum Discussion
Get value from row in the same table based on last date
How can I return the amount based on last date for a specific customer using DAX?
Here is a sample data:
In this case, I want Customer A for each Services Contract to return its nearest last Software Contract net amount, which would be 5,000. On the other hand, Customer B wouldn't return anything since there are no past dates, just future ones.
The measure should work only for Services Contracts, which is why I set the IF statement to return 0 when Type of Contract is not Services.
The closest I could get was to use EARLIER below, but that is returning me the Total Net Amount of all Software Contracts per customer in a cumulative way, instead of being just the last entry based on date.
Anyone has had a similar challenge and/or could point me in the right direction?
Hey,
assuming you want to create a calculated column you can solve your question by doing this:
- create a var that stores the current customer
- create a var stat stores the currnent date
- create a var stat stores the type of contract
use the above information to find the "max" date of all the dates that are smaller than the current date, store this date in a variable
The DAX statement will look similar to this
var latestDate = CALCULATE( MAX('tablename'[Startdate]) ,FILTER( ALL('tablename') ,'tablename'[Parent Customer] = varCurrentCustomer && ... && 'tablename'[Start Date] < varCurrentDate )Now use this variable together with the other variables to determine the Net Amount, just change part to the condition inside the FILTER() to
,'tablename'[Parent Customer] = varCurrentCustomer && ... && 'tablename'[Start Date] = latestDate
If you have further questions please provide sample data, create a pbix file, upload the file to onedrive or dropbox and share the link to the file
Regards,
Tom
4 Replies
- TomMartens
Super User
Hey,
assuming you want to create a calculated column you can solve your question by doing this:
- create a var that stores the current customer
- create a var stat stores the currnent date
- create a var stat stores the type of contract
use the above information to find the "max" date of all the dates that are smaller than the current date, store this date in a variable
The DAX statement will look similar to this
var latestDate = CALCULATE( MAX('tablename'[Startdate]) ,FILTER( ALL('tablename') ,'tablename'[Parent Customer] = varCurrentCustomer && ... && 'tablename'[Start Date] < varCurrentDate )Now use this variable together with the other variables to determine the Net Amount, just change part to the condition inside the FILTER() to
,'tablename'[Parent Customer] = varCurrentCustomer && ... && 'tablename'[Start Date] = latestDate
If you have further questions please provide sample data, create a pbix file, upload the file to onedrive or dropbox and share the link to the file
Regards,
Tom
- Ashish_Mathur
Hi,
Try this calculated column formula
=LOOKUPVALUE(ContractLines[Net Amount],ContractLines[Start Date],CALCULATE(Max(ContractLines[Start Date]),FILTER(ContractLines,ContractLines[Parent Customer]=EARLIER(ContractLines[Parent Customer])&&ContractLines[Type of Contract]<>"Services"&&ContractLines[Start Date]<EARLIER(ContractLines[Start Date]))),ContractLines[Parent Customer],ContractLines[Parent Customer])
If this does not work, then share the link from where i can download your PBI file.
Thanks very much for the quick replies!
Ashish_Mathur, when I tried your LOOKUP calculated column, it says a "table supplied with multiple values was supplied when a single value was expected".
Anonymous, looks like your code is doing it. The only thing I had to do is to check "Don't Summarize" under my table options, because if it is set to SUM then I get some weird numbers. I'm curious, though, to understand why you used ALL() to iterate over all table values. In any case, I'll mark your aswer as the solution to the problem. :)
- Ashish_Mathur
Hi,
It works absolutely fine. This is the calculated column formula
=if([Type of Conteact]="Services",LOOKUPVALUE(ContractLines[Net Amount],ContractLines[Start Date],CALCULATE(Max(ContractLines[Start Date]),FILTER(ContractLines,ContractLines[Parent Customer]=EARLIER(ContractLines[Parent Customer])&&ContractLines[Type of Conteact]<>"Services"&&ContractLines[Start Date]<EARLIER(ContractLines[Start Date]))),ContractLines[Parent Customer],ContractLines[Parent Customer]),BLANK())
Hope this helps.
