Forum Discussion
Need Line Chart Trend Comparison by Years Calculate To Start at Zero
I am doing a Line Chart Trend Comparison by Yearsusing a measure to Calculate the cumulative sales. I put this in the 'Legend' and it works, sort of but I want the next year's value to Start at Zero.
[MEASURE]
Cumulative Sales =
VAR _maxdate = MAX(Bookings[Book Date])
RETURN
CALCULATE(SUM(Bookings[Bookings Net Value]),ALLSELECTED(Bookings),Bookings[Book Date] <= _maxdate)
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.
2 Replies
- Divyaraj_RathodHelper II
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.
- icecapcNew Member
That worked perfectly. Thank you!!