Forum Discussion
Resolver IDatediff 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.
Community SupportHi 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.
SilvardHi 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