Forum Discussion

pgenero's avatar
pgenero
Frequent Visitor
2 years ago
Solved

Average Days between 2 tables

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 t...
  • AmiraBedh's avatar
    2 years ago

    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