Datediff between two inactive tables

[Silvard]

Problem:

How do we create a measure that can show the historical datediff over time in a line graph using month or year as x-axis between the order date and invoice date when the two tables are only linked through a facts table and both dates have inactive relationships with the calendar table?

Setup:

- Facts Table linked to Orders Table via OrderId

- Facts Table Linked to Invoice Table via InvoiceID.

- Invoice date and Orders date both have inactive relationships with the Calendar table.

[v-hashadapu · Accepted solution]

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.

[Silvard]

Hi there!

thanks for helping me try to solve this issue. I accepted it as a solution but have since realised that it's not pulling the correct results.

I've asked chatgpt as well and received below response.

have you got any other idea how to solve it?

like I've tried to create a virtual table using selectcolumns,filter and calculatetable but unfortunately this exceeds the available resources and I never get to see if it works.

"FIRSTNONBLANK: This function returns the first value that is not blank from the specified column (in this case, Orders[OrderDate]) within the given filter context. It scans the rows one by one, from the start, and stops when it finds the first non-blank value. It does not return all values—just the first one that isn't blank.

The 1 in FIRSTNONBLANK(Orders[OrderDate], 1): This is the expression to evaluate when determining the first non-blank value. It could be any expression, but since 1 is a constant, it is effectively irrelevant in this case. The function just checks for the first row where Orders[OrderDate] is not blank and returns that value.

Does it stop after the first non-blank?

Yes, it stops after finding the first non-blank value. It doesn't continue

[shafiz_p]

Hi @Silvard  It is hard to give specific solution without representative data and desired output. Anyway, you can activate inactive relationship temporarily using USERELATIONSHIP function.

For example:

DateDiffMeasureWithCategory = 
VAR OrderDate = CALCULATE(
    MAX(Orders[OrderDate]),
    USERELATIONSHIP(Orders[OrderDate], Calendar[Date]),
    Orders[Category] = "SpecificCategory"  -- Replace with your specific category
)
VAR InvoiceDate = CALCULATE(
    MAX(Invoices[InvoiceDate]),
    USERELATIONSHIP(Invoices[InvoiceDate], Calendar[Date])
)
RETURN
DATEDIFF(OrderDate, InvoiceDate, DAY)

 Now you can use month or year from calendar table in line visual and above similar measure.

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[Silvard]

Hi @shafiz_p 

thanks for your input.

I'm looking for the average datediff in context of the x axis (year or month) and I don't believe using MAX achieves this.

[shafiz_p]

You could try this:

AverageDateDiffWithCategory =
    AVERAGEX(
        FILTER(
            Orders,
            Orders[Category] = "SpecificCategory"  -- Replace with your specific category
        ),
        [DateDiffMeasureWithCategory]
    )

Where [DateDiffMeasureWithCategory] is the measure you have created previously. Need to remove category part from the measure.

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

[Silvard]

I forgot to add to the problem:

We only want the datediff when the order date is from a specific category. In other words, we need a filtered Orders table.

How would we do that?

appreciate your help!