Change DAX calculated column using EARLIER Function

[jludwick]

Hello,

I recently read that you should not use the EARLIER function. I recently inherited a dataset file that uses this function and it keeps crashing, likely due to the EARLIER function. I am trying to convert this formula into the proper format using variables but I'm having some trouble. 

The current code is set up like so to get the cumulative balance as of the most recent invoice. How can I adjust this to remove the EARLIER function but get the same result?

_Balance Cumulative =
calculate(sum(Invoices[Balance]),
          ALLEXCEPT(Invoices,Invoices[Customer ID + Company Desc]),
          filter(
             all(Invoices[Invoice Date]),Invoices[Invoice Date] <= 
                 EARLIER(Invoices[Invoice Date])))

[ThxAlot]

In a calculated column, it's highly recommended to use VAR to subsitute for EALIER/ERLIEST for better readability and maintainability,

_Balance Cumulative =
VAR __dt = Invoices[Invoice Date]
RETURN
    CALCULATE(
        SUM( Invoices[Balance] ),
        ALLEXCEPT( Invoices, Invoices[Customer ID + Company Desc] ),
        Invoices[Invoice Date] <= __dt
    )

[Anonymous · Accepted solution]

Hi @jludwick ,

If you want to create a calculated column, you can try:

_Balance Cumulative =
var _a=Invoices[Invoice Date]
return
calculate(sum(Invoices[Balance]),
          ALLEXCEPT(Invoices,Invoices[Customer ID + Company Desc]),
          filter(
             all(Invoices[Invoice Date]),Invoices[Invoice Date] <= _a
                 ))

If you want to create a measure, you can try:

_Balance Cumulative =
calculate(sum(Invoices[Balance]),
          ALLEXCEPT(Invoices,Invoices[Customer ID + Company Desc]),
          filter(
             all(Invoices[Invoice Date]),Invoices[Invoice Date] <= 
                 MAX(Invoices[Invoice Date])))

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.