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

[Roger]

In the attached Pbix I want to create a measure to
to replace the result from the calculated column " DIFF in day JOB and unit NO"
but fail to find the create the measure. I always get an error with the earlier() function.
the result of the calculated column is correct.

maybe there is another simplier solution to solve this.
aim is to calculate the number of days for a JOB & UNIT_NO combination.
Once the measure is created I can copy it to calculate the difference of the km_meter

any help is greatly appreciated
Roger
DAX for column does not work as measure.pbix (279.7 KB)

[Keith]

Hi @Roger

Just wondering if your date format is part of the issue. One column has the “-” and other has the “/”. Having a standard format

Have you tried using Chatgpt or another AI tool to help you? If you have Chatgpt 4 and use (ADA) option.

Please review the webinar event: Deep Exploration of Advanced Data Analysis (ADA) by Open AI

Dashboard (enterprisedna.co)

I hope this helps.
Keith

[Roger]

Keith
thanks for your answer, but the dax works fine in the calculated column, so I don’t think
that’s the problems, I did use the chatTGPT for alternative for the earlier function but with no success. I will review the webinar

kind regards

Roger

[Greg]

Hi @Roger. I’m guessing that the EARLIER function works in row context (your calculated column) but not in filter context (your measure). I’ve been using Power BI several hours just about every day for the past 4-5 years and have never used (and only very rarely seen) the EARLIER function … AFAIK it’s a best practice to not use it (I thought it was legacy and that it had been deprecated). Greg

[Roger]

Honnered to get an answer from Greg and thanks for the reply.
Does this mean that I can not create a measure to solve this problem?
Is there a workarround for this to not use the earlier function ?

thanks in advance

Roger

[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)

[Roger]

Greg
thanks for the solution, it works great and it’s a simple pattern to use in the future pbix files.
As always great support on the platform and an impressive content to learn from

kind regards

Roger