Forum Discussion
Measure For Last Invoice Date by Customer?
I have been asked to provide the last order date for each customer in a Power BI report. I am connecting to an existing semantic model, so I am unable to edit the model in Power Query or add a calculated column to the semantic model. I was hoping to accomplish this through DAX in a measure. What I believe to be the relevant information is as follows:
Tables:
factSales - shippingCustomerKey (integer), invoiceDateKey (integer, assigned as YYYYMMDD)
dimCalendar - dateKey (integer as YYYYMMDD), calendarDate(date, MM/DD/YYYY)
dimCustomer - customerKey (integer), customerNumber (text)
Relationships:
factSales[invoiceDateKey] * : 1 dimCalendar[dateKey]
factSales[shippingCustomerKey] * : 1 dimCustomer[customerKey]
Example Data:
factSales
| invoiceLineKey | invoiceNumber | invoiceLineNumber | invoiceDateKey | shippingCustomerKey | productKey | salesAmount | shippedQuantity |
| 1 | 123456 | 1 | 20250206 | 1 | 1 | 15.00 | 1 |
| 2 | 123456 | 2 | 20250206 | 1 | 2 | 30.00 | 3 |
| 3 | 123456 | 3 | 20250206 | 1 | 3 | 20.00 | 5 |
| 4 | 234567 | 1 | 20250209 | 2 | 4 | 39.00 | 3 |
| 5 | 345678 | 1 | 20250217 | 3 | 1 | 60.00 | 4 |
| 6 | 345678 | 2 | 20250217 | 3 | 5 | 17.00 | 1 |
| 7 | 456789 | 1 | 20250224 | 1 | 6 | 42.00 | 6 |
dimCustomer
| customerKey | customerNumber | customerName | customerState |
| 1 | 1234567890 | Chris P Bacon | IA |
| 2 | 9876543210 | Ella Vader | CT |
| 3 | 1029386754 | Joe King | NV |
I don't think dimCustomer is directly relevant, but including it anyway in case it is
dimCalendar
| dateKey | calendarDate |
| 20250206 | 2/6/2025 |
| 20250209 | 2/9/2025 |
| 20250217 | 2/17/2025 |
| 20250224 | 2/24/2025 |
Desired Output:
| factSales[shippingCustomerKey] | dimCustomer[customerNumber] | dimCustomer[customerName] | dimCustomer[customerState] | lastInvoiceDate |
| 1 | 1234567890 | Chris P Bacon | IA | 2/24/2025 |
| 2 | 9876543210 | Ella Vader | CT | 2/9/2025 |
| 3 | 1029386754 | Joe King | NV | 2/17/2025 |
In looking through these forums, I found the accepted solution here (https://community.fabric.microsoft.com/t5/Desktop/Create-a-max-date-measure-for-each-id/td-p/2992029) and modified it as follows:
lastInvoiceDate =
VAR _dates = ALLSELECTED(factSales[invoiceDateKey])
VAR _result = CALCULATE(MAX(factSales[invoiceDateKey]), REMOVEFILTERS(factSales), VALUES(factSales[shippingCustomerKey]), _dates)
RETURN _result
In my opinion, this works great as it outputs the factSales[invoiceDateKey] that corresponds to the most recent invoice date for each customer. However, during testing, the business users requested that this be converted into MM/DD/YYYY format. I initially tried changing the data type of the column, hoping that Power BI would recognize it as a valid date format, but all I got back was the text representing the date format I selected (e.g. instead of displaying 20080402 as 4/2/2008, it displayed MM/DD/YYYY). I tried parsing out the year, month, and day from _result and forming a string with "-" or "/" as separators and received an error (using too much memory I believe). Same result when I tried to parse those values and put them into DATE to form an actual date. I also tried using ADDCOLUMNS to modify my factSales table to include a column that parsed the invoiceDateKey into a date, but I couldn't figure out how to use it later.
Is there anything else I can try in order to return a date or a string in a mmddyyyy format with / or - as separators? I'm a novice with DAX and my attempts to pull back the date from my calendar dimension were fruitless. Any assistance would be greatly appreciated. Thanks in advance!
-Tom
You can use LOOKUPVALUE
lastInvoiceDate = VAR _dates = ALLSELECTED ( factSales[invoiceDateKey] ) VAR _MaxDate = CALCULATE ( MAX ( factSales[invoiceDateKey] ), REMOVEFILTERS ( factSales ), VALUES ( factSales[shippingCustomerKey] ), _dates ) VAR _result = LOOKUPVALUE ( dimCalendar[calendarDate], dimCalendar[dateKey], _MaxDate ) RETURN _result
2 Replies
- johnt75Super User
You can use LOOKUPVALUE
lastInvoiceDate = VAR _dates = ALLSELECTED ( factSales[invoiceDateKey] ) VAR _MaxDate = CALCULATE ( MAX ( factSales[invoiceDateKey] ), REMOVEFILTERS ( factSales ), VALUES ( factSales[shippingCustomerKey] ), _dates ) VAR _result = LOOKUPVALUE ( dimCalendar[calendarDate], dimCalendar[dateKey], _MaxDate ) RETURN _result- TomM_5Frequent Visitor
This is outstanding as I had no idea that LOOKUPVALUE could be used like this. However, when I add the LOOKUPVALUE line and adjust my RETURN to return the calendarDate value, my visual becomes a black square with a "Query has exceeded the available resources" error. Is there another way I could try to get this information that won't throw that error?
-Tom