Running Total Problem

[davegw]

I have a data set with counts per number of weeks before a certain date. I want to visualsie this as a running total.

I'm using this as my measure:-

tickets running total in weeks_before 2 =

CALCULATE(

    SUM('sales_over_time (2)'[tickets]),

    FILTER(

        ALLSELECTED('sales_over_time (2)'[weeks_before]),

        ISONORAFTER('sales_over_time (2)'[weeks_before], MAX('sales_over_time (2)'[weeks_before]), ASC)

    )

)

My result is only partially correct, it looks like the picture below. The problem is the currently selected data (from slicers) contains no records for weeks 29 to 52 but these week numbers exist for rows that are filtered out by the slicer. This seems to be causing an error in the calculation. What am I missing?

[v-piga-msft]

Hi @davegw ,

Have you solved the problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share some data sample and your desired output so that we could help further on it.

Best  Regards,

Cherry

[Cmcmahan · Accepted solution]

It is working correctly.  When you use ALLSELECTED in your measure, it only returns values that are available to the current visual.  Since you have filtered data from weeks 29-52 via slicer, the data isn't used.

You can try replacing ALLSELECTED with ALL or ALLEXCEPT, though you may have to reapply other filters as necessary.

tickets running total in weeks_before 2 =
CALCULATE(
    SUM('sales_over_time (2)'[tickets]),
    FILTER(
        ALLEXCEPT('sales_over_time (2)', 'sales_over_time (2)'[weeks_before]),
        ISONORAFTER('sales_over_time (2)'[weeks_before], MAX('sales_over_time (2)'[weeks_before]), ASC)
    )
)