cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

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

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.
2 REPLIES 2
Responsive Resident

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.

Frequent Visitor

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.

Helpful resources

Announcements

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

Power BI May 2023 Update

Find out more about the May 2023 update.

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors
Top Kudoed Authors