The DAX works well in a calculated column but not as a measure

[Greg]

Hi @Roger.

As I noted before, I’m unfamiliar with the EARLIER function so don’t know any workarounds. That being said, there are mutiple ways to refer to other rows in a visual that may be useful. Here’s an article I posted a few years ago that may provide some insight:

Previous Row Value

Often in a Power BI visual one wants to get the value from the previous row to use in a calculation in the current row (e.g., to see if there’s a change between the previous value and the current value). The Previous Row Value DAX pattern can be of help when faced with this issue. As with most situations in Power BI, there are many ways to solve the same problem: here are a couple: Method 1: Add DAX Index Column 1-Add a [DAX Index] calculated column to your table DAX Index = // DAX PATTERN …

As well, possibly the recently released WINDOW functions in DAX may help.

Here’s a quick draft of a measure that produces the same result, but not sure I understand your existing logic (there seem to be multiple rows for each UNIT NO/JOB combination, so …)

Here’s the measure code:

DIFF as a Measure = 
VAR _UnitNo = SELECTEDVALUE( WO_JOBS[UNIT_NO] )
VAR _Job = SELECTEDVALUE( WO_JOBS[JOB] )
VAR _JobOpenDate = SELECTEDVALUE( WO_JOBS[JOB_OPEN_DATE] )
VAR _PreviousJobOpenDate =
    CALCULATE(
        MAX( WO_JOBS[JOB_OPEN_DATE] ),
        FILTER(
            ALL( WO_JOBS ),
            WO_JOBS[UNIT_NO] = _UnitNo
                && WO_JOBS[JOB] = _Job
                && WO_JOBS[JOB_OPEN_DATE] < _JobOpenDate
        )
    )
VAR _Result = DATEDIFF( _PreviousJobOpenDate, _JobOpenDate, DAY )

RETURN
    _Result

Perhaps one of these will provide useful direction.

Also, your [Calendar] table was not marked as a date table.

Hope it helps.
Greg
eDNA Forum - Previous Alternatives.pbix (244.5 KB)