Forum Discussion

icecapc's avatar
icecapc
New Member
1 month ago
Solved

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...
  • Divyaraj_Rathod's avatar
    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.