Forum Discussion

Silvard's avatar
Silvard
Icon for Resolver I rankResolver I
1 year ago
Solved

Datediff between two inactive tables

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...
  • v-hashadapu's avatar
    v-hashadapu
    1 year ago

    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.

shafiz_p's avatar
shafiz_p
Icon for Super User rankSuper User
1 year ago

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's avatar
Silvard
Icon for Resolver I rankResolver I
1 year ago

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's avatar
    shafiz_p
    Icon for Super User rankSuper User
    1 year ago

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