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.
Resolver IHi 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.
Super UserYou 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!!