Forum Discussion
Solved
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 calcul...
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
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
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
