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.
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]
Thank you very much