topic Re: Datediff between two inactive tables in DAX Commands and Tips

Datediff between two inactive tables

Silvard — Wed, 18 Dec 2024 03:41:12 GMT

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.

Re: Datediff between two inactive tables

Silvard — Wed, 18 Dec 2024 03:54:01 GMT

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!

Re: Datediff between two inactive tables

shafiz_p — Wed, 18 Dec 2024 04:20:36 GMT

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

Re: Datediff between two inactive tables

Silvard — Wed, 18 Dec 2024 04:28:57 GMT

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.

Re: Datediff between two inactive tables

v-hashadapu — Wed, 18 Dec 2024 05:34:57 GMT

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.

Re: Datediff between two inactive tables

shafiz_p — Wed, 18 Dec 2024 08:40:09 GMT

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!!

Re: Datediff between two inactive tables

Silvard — Wed, 18 Dec 2024 10:08:31 GMT

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