Forum Discussion
pgenero
2 years agoFrequent Visitor
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...
- 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
AmiraBedh
2 years agoSuper User
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