Excel Power Pivot
Lookup Table = Calendar;
Data Table = Sales
Pivot table Rows: Calendar[Year]
Pivot table Values: [Measure] = CALCULATE(SUM(Sales[Amount]); Calendar[Date] < DATE(2003;01;01))
Measure does not respect the filter context Year and returns:
Year 2002 -> sum of sales from the earliest date in Calendar to 01/01/2003;
Year 2003 -> the same
As Date column is not in the filter context – Year , I would expect the Date filtration added to the Filter context. The Measure should return then:
Year 2002 -> sum of 2002 sales;
Year 2003 -> 0
Why does it happen?
seaman47 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
Your measure is summing sales for all years up to January 1, 2003, instead of filtering by the year in your Pivot Table.
Adjust the measure to respect the year filter:
[Measure] =
CALCULATE(
SUM(Sales[Amount]),
FILTER(
Calendar,
Calendar[Date] < DATE(2003, 01, 01) &&
Calendar[Year] = VALUES(Calendar[Year])
)
)
1