Interval of time calculation breaks when adding order details PowerBI DAX

I have an excel file containing orders and related info. The one I need this time is the interval of time between an order and the previous one in time. Several orders can have the same delivery date. I managed to make it work when only viewing the dates themselves, like so:

As soon as I introduce just the order number, or really even just try to average the gap measure, it only outputs 0s, like so:

The gap is set to look for the highest value within the a duplicate column, with the condition that it is inferior to the dates_measure value. It works well, I can shuffle the rows around without messing the values up. I don't understand why adding duplicates (or not really) would stop it from working.

Here's the code associated with the highest date before:

Date la plus élevée inférieure = 
VAR CurrentDate = 'Excel du Report'[Date_measure]

VAR HigherDate =
    CALCULATE (
        MAX ( 'Excel du Report'[Jour calendaire] ),
        FILTER (
            ALL('Excel du Report'[Jour calendaire]),  
            'Excel du Report'[Jour calendaire] < CurrentDate
        )
    )
RETURN
     IF (
        ISBLANK(HigherDate),
        CurrentDate, 
        HigherDate
    )

Here's the gap one :

Gap_between_dates = 
VAR CurrentDeliveryDate = [Date_measure]
VAR PreviousDeliveryDate = [Date la plus élevée inférieure]

-- Calculer la différence entre les dates de livraison
VAR Difference = CurrentDeliveryDate - PreviousDeliveryDate

-- Calculer la somme du numéro de jour dans la semaine de ARGH et de la différence
VAR TotalDays = WEEKDAY(PreviousDeliveryDate, 2) + Difference

-- Vérifier si le total dépasse 5, indiquant un week-end
VAR AdjustedTotalDays =
    IF(
        TotalDays > 5,
        Difference - (FLOOR(TotalDays / 7,1) * 2),
        Difference
    )

RETURN
    AdjustedTotalDays * 1

The end use for that thing is a global average, that may be sliced by receiving client. Like, we delivered to this client once every X days on average, to that one Y on average, and globally a client was delivered every Z days... well you get it. No clue how I'll deal with duplicate dates (they'll show the same gap/interval N times) but a distinct on the dates should do the trick I believe.

Thanks for reading and I hope for any kind of guidance!

I tried looking online. I tried making the calculation another way. Nothing worked.

Sorry this section is kinda empty, my head is basically empty as well, am drained out.

I'll feel real stupid if it's been answered somewhere already, can't find it for the life of me.

Solution

I cannot comment yet, but I would like to get started with this measure for the first and second requirement and then build upon it to get everything right (you can comment on my post and I can comment on this one also). If I am totally wrong, please say so and I can edit this post.

For the average gap per customer, you could do something like this:

Measure 8 = 
VAR AverageGapByCustomer = AVERAGEX(
    'Table (2)',
    VAR CurrentDate = 'Table (2)'[Order Date]
    VAR PreviousDate = MAXX(
    FILTER('Table (2)', [Order Date] < CurrentDate),
    [Order Date]
)
    VAR GAP = DATEDIFF(PreviousDate, CurrentDate, DAY)
    RETURN GAP
)
RETURN AverageGapByCustomer

(!): I use this measure on a simple table visual with CustomerID added as column to create a filter context.

In essence, it averages the 'gap' values for a specific customer ID based on the 'Table'.
The AVERAGEX iterator iterates over the filtered 'Table (2)' for a specific customer id, which is only a subset of the whole table. This is because I added the Customer ID as a filter context.
For each row in the table:
- It selects the current date for that row
- It filters the 'filtered table' to get the previous order date by using the maxx function. A calculate(...) would create a context transition and give the wrong date.
- It calculates the gap (not adjusted for weekends yet) and stores it
Finally, it averages all of them together to get a single 'average' value for a customer.

SAMPLE DATA (including a calculated column to verify the results): | Order Date | CustomerId | Gaps | |-------------------------|------------|------| | friday 1 march 2024 | 1 | | | saturday 2 march 2024 | 2 | | | friday 15 march 2024 | 2 | 13 | | thursday 28 march 2024 | 3 | | | monday 1 april 2024 | 1 | 31 | | wednesday 3 april 2024 | 3 | 6 | | wednesday 3 april 2024 | 1 | 2 |

RESULT: | Customer Id | Measure | |-------------|---------| | 1 | 16,50 | | 2 | 13,00 | | 3 | 6,00 |

The global average would be 13.

Perhaps an easier way is to just create a calculated column and calculate the gaps there?

Gaps = 
VAR CurrentCustomer = 'Table (2)'[CustomerId]
VAR CurrentDate = 'Table (2)'[Order Date]
VAR _table = MAXX(
    FILTER('Table (2)', [CustomerId] = CurrentCustomer && [Order Date] < CurrentDate),
    [Order Date]
)
VAR GAP = DATEDIFF(_table, CurrentDate, DAY)
RETURN GAP