Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zarmonas
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
bhelou
Responsive Resident
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.

 

 

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

error.PNG

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.