Forum Discussion

TomM_5's avatar
TomM_5
Frequent Visitor
1 year ago
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 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

invoiceLineKeyinvoiceNumberinvoiceLineNumberinvoiceDateKeyshippingCustomerKeyproductKeysalesAmountshippedQuantity
11234561202502061115.001
21234562202502061230.003
31234563202502061320.005
42345671202502092439.003
53456781202502173160.004
63456782202502173517.001
74567891202502241642.006

 

dimCustomer

customerKeycustomerNumbercustomerNamecustomerState
11234567890Chris P BaconIA
29876543210Ella VaderCT
31029386754Joe KingNV

I don't think dimCustomer is directly relevant, but including it anyway in case it is

 

dimCalendar

dateKeycalendarDate
202502062/6/2025
202502092/9/2025
202502172/17/2025
202502242/24/2025

 

Desired Output:

factSales[shippingCustomerKey]dimCustomer[customerNumber]dimCustomer[customerName]dimCustomer[customerState]lastInvoiceDate
11234567890Chris P BaconIA2/24/2025
29876543210Ella VaderCT2/9/2025
31029386754Joe KingNV2/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

  • 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_5's avatar
      TomM_5
      Frequent 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