Is there a way to calculate difference between dates in one column using DirectQuery?

[zarmonas]

Hello,

So I am having an issue writing formulla to calculate difference between dates in a single column, grouping by values in another column. The problem is, that my report is in DirectQuery mode, so lots of functions are not supported. In import mode I used this formula:

Delivery Interval Days =

VAR temp =

    TOPN (

        1,

        FILTER (

            'Actual Delivery',

            'Actual Delivery'[SHIP_TO] = EARLIER ( 'Actual Delivery'[SHIP_TO] )

                && 'Actual Delivery'[DELIVERY_END_DATE&TIME_ACTUAL] < EARLIER ( 'Actual Delivery'[DELIVERY_END_DATE&TIME_ACTUAL] )

        ),

        [DELIVERY_END_DATE&TIME_ACTUAL], DESC

    )

RETURN

    DATEDIFF ( MINX ( temp, [DELIVERY_END_DATE&TIME_ACTUAL] ), 'Actual Delivery'[DELIVERY_END_DATE&TIME_ACTUAL], DAY )

However, function EARLIER cannot be used inside CALCULATE or FILTER function while in DirectQuery mode.
Any Ideas? 🙂

Thanks in advance.

[bhelou]

In DirectQuery mode, you can try using the TREATAS function to simulate the EARLIER function. Here is an example of how to modify your formula using TREATAS:
Delivery Interval Days =
VAR temp =
    TOPN (
        1,
        FILTER (
            'Actual Delivery',
            TREATAS ( { 'Actual Delivery'[SHIP_TO] }, 'Actual Delivery'[SHIP_TO] )
                && 'Actual Delivery'[DELIVERY_END_DATE&TIME_ACTUAL] < 'Actual Delivery'[DELIVERY_END_DATE&TIME_ACTUAL]
        ),
        [DELIVERY_END_DATE&TIME_ACTUAL], DESC
    )
RETURN
    DATEDIFF (
        MINX ( temp, [DELIVERY_END_DATE&TIME_ACTUAL] ),
        'Actual Delivery'[DELIVERY_END_DATE&TIME_ACTUAL],
        DAY
    )

////

The TREATAS function creates a virtual filter that temporarily replaces the current filter context with a new one. In this case, we are using TREATAS to simulate the effect of EARLIER by passing the value of 'Actual Delivery'[SHIP_TO] to the filter context of the FILTER function.

Note that TREATAS can be computationally expensive and may slow down the query. Therefore, it is recommended to use it only when necessary and to test the performance of the modified formula on a small subset of data before applying it to the entire dataset.

[zarmonas]

The formula itself has no errors, but when this column is used in a table visual, I get error

Tried to use chat GPT to help me with this issue, but I always ended up in a circle of not supported functions.