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.

 

[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

  • 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.