Average Days between 2 tables

[pgenero]

Hi! I have a model with 4 tables: Dates | Accounts | Sales | Samples.
What I want to do is to calculate the difference of days between the first sale for each account and the first sample (only if the sample was delivered before the date of the first sale). Then I want to average those days for all the accounts that have made their first purchase in the month provided.
The final goal is to display something like this: a table with month year in one column, then another column with the average number of days between the sample date and the sale date.
I was able to create in memory a table using Summarize and /or Addcolumns, where I can get the first date from Sales and Samples, and then also the AVG of days. But I-m not able to display those days in the desired month.

This is my code:
MeasureName =

VAR _TABLE =

    SUMMARIZE(

        Account,

        Account[Customer_Number],

        "@firstSale",

        CALCULATE(

            FIRSTDATE(SALES[SalesDate]),

            ALL(SALES),

            VALUES(SALES[Customer_Number])

        ),

        "@firstSample",

        CALCULATE(

            FIRSTDATE(SAMPLES[SampleDate]),

            ALL(SAMPLES),

            VALUES(SAMPLES[Customer_Number])

        )

    )

VAR _result =

AVERAGEX(

   _TABLE,

   DATEDIFF([@firstSample], [@firstSale], DAY)

)

RETURN

_result

Currently this formula display the average (note that there is no restriction about the negative days, which is not my actual problem) in the month when the LAST sales happen. I need to display the number only in the month of the first sale.

[AmiraBedh · Accepted solution]

You current measure calculates the average difference in days between the first sample and the first sale for each customer,
but it does not restrict the calculation to the specific month of the first sale.

MeasureName =

VAR SelectedMonthYear = MAX(Dates[MonthYear]) // Assuming you have a MonthYear column in your Dates table

VAR _TABLE =
    SUMMARIZE(
        Account,
        Account[Customer_Number],
        "@firstSale",
        CALCULATE(
            FIRSTDATE(SALES[SalesDate]),
            ALL(SALES),
            VALUES(SALES[Customer_Number])
        ),
        "@firstSample",
        CALCULATE(
            FIRSTDATE(SAMPLES[SampleDate]),
            ALL(SAMPLES),
            VALUES(SAMPLES[Customer_Number])
        )
    )

VAR FilteredTable = 
    FILTER(
        _TABLE,
        MONTH([@firstSale]) = MONTH(SelectedMonthYear) && YEAR([@firstSale]) = YEAR(SelectedMonthYear)
        && [@firstSample] < [@firstSale]
    )

VAR _result =
    IF(
        ISEMPTY(FilteredTable),
        BLANK(),
        AVERAGEX(
            FilteredTable,
            DATEDIFF([@firstSample], [@firstSale], DAY)
        )
    )

RETURN
    _result