How to Fix/Improve this DAX

[AndreaCBS]

Avg=

VAR X=

    SUMX(

    FILTER(

        'Sales Invoices',

        'Sales Invoices'[Date] < MAX('Sales Invoices'[Date])

    ),

    'Sales Invoices'[AmountCRC]

)

RETURN

    DIVIDE(X, [Quantity])

The idea behind this DAX is to calculate the Avg, but it must take into consideration the last cost prior to the filter date of the period for which the margin is being calculated.

[some_bih · Accepted solution]

Hi @AndreaCBS without file, model, logic... it is hard to spot issue.

Still, try v2 below

Avg v2=

VAR __max_date=MAX('Sales Invoices'[Date])

VAR X=
SUMX(
FILTER(
'Sales Invoices',
'Sales Invoices'[Date] < __max_date
),
'Sales Invoices'[AmountCRC]
)
RETURN
DIVIDE(X, [Quantity])