Forum Discussion
Need Line Chart Trend Comparison by Years Calculate To Start at Zero
- 1 month ago
The reason each year's line keeps climbing instead of restarting at zero is that ALLSELECTED(Bookings) clears the filter context coming from your chart's Year legend/axis (since Year comes from a column on the Bookings table itself), so the measure accumulates across ALL years up to the max date, not just the selected year.
Try re-applying an explicit Year filter so ALLSELECTED only clears the month-level context, not the year:
Cumulative Sales =
VAR _maxdate = MAX(Bookings[Book Date])
VAR _yr = YEAR(_maxdate)
RETURN
CALCULATE(
SUM(Bookings[Bookings Net Value]),
ALLSELECTED(Bookings),
Bookings[Book Date] <= _maxdate,
YEAR(Bookings[Book Date]) = _yr
)
This keeps the "accumulate within the visual's selection" behavior of ALLSELECTED for the month axis, while forcing the running total to reset to zero at the start of each year.
That worked perfectly. Thank you!!