Average() based on Start Date & End Date

[alhazen]

Hello guys,

i need your help

how to calculate average by start date and end date?

This is the source table:

and this is the table for calculation

I used this formula, but it didn't work.

AVERAGE(
SELECT(
Sales[unit_sold],
AND(
[Product]=[Product],
[start_date]>=ANY(Sales[date]),
[end_date]<=ANY(Sales[date])
)
)
)

Thank you very much for your help.

[dbaum · Accepted solution]

There may be other issues, but one detail is that likely you need to distinguish the references to the [Product] columns between the two tables. Analogously, you may likely need [_THISROW] also for your references to [start_date] and [end_date] columns. Within the SELECT function the default context for any column reference is the table from the function's first argument (e.g., Sales)--not the table where the expression happens to be.

[Product]=[_THISROW].[Product]  

[alhazen]

Thank you very much