Datediff between two inactive tables

Hi Silvard ,Thank you for reaching out to Microsoft Fabric Community Forum.

Please try this modified DAX query:

AverageOrderToInvoiceDiff =

AVERAGEX (

    FILTER (

        ALL ( Orders ),  -- Remove row-level filters from Orders table (but not calendar context)

        Orders[Category] = "SpecificCategory"  -- Filter for a specific category

    ),

    VAR _OrderDate =

        CALCULATE (

            FIRSTNONBLANK ( Orders[OrderDate], 1 ),  -- Use FIRSTNONBLANK to get order date in context

            USERELATIONSHIP ( Orders[OrderDate], Calendar[Date] )

        )

    VAR _InvoiceDate =

        CALCULATE (

            FIRSTNONBLANK ( Invoice[InvoiceDate], 1 ),  -- Use FIRSTNONBLANK to get invoice date in context

            USERELATIONSHIP ( Invoice[InvoiceDate], Calendar[Date] )

        )

    RETURN

        DATEDIFF ( _OrderDate, _InvoiceDate, DAY )  -- Calculate the difference (use MONTH/YEAR if needed)

)

The above measure will calculate the average difference between the order date and the invoice date for the specific category in the context of each time period (Month or Year).

You can add year or month on x-axis and add AverageOrderToInvoiceDiff measure to y-axis. We can use a slicer for Orders[Category] to filter by a specific category.

If this helps, please mark it ‘Accept as Solution’, so others with similar queries may find it more easily. If not, please share the details.