How to stop accumulated measure

[UCR522]

I want to conduct a study on the lifespan of products. For this purpose, I have created a measure that accumulates sales by years elapsed since the launch.

The formula is as follows:

SUM('Sales'[Sales Amount]),
FILTER(
ALL('Years since launch'),
'Years since launch'[Years since launch] <= MAX('Years since launch'[Years since launch])
)

The resulting graphic will be this

What happens? If we look at the example of 2016, the curve remains flat from year 8 onwards because from 2016 to 2023, 7 years have elapsed and there are no sales from year 8 onwards.

How should the DAX formula for accumulated sales be so that when the value of the x-axis is greater than the difference between today and the launch year, the calculation is interrupted?

Thanks in advance

Javier

[123abc · Accepted solution]

You can use this modified Dax:

Accumulated Sales =
CALCULATE(
SUM('Sales'[Sales Amount]),
FILTER(
ALL('Years since launch'),
'Years since launch'[Years since launch] <= MAX('Years since launch'[Years since launch]) &&
'Years since launch'[Years since launch] + [Years Elapsed] >= YEAR(TODAY())
)
)

In this formula, I've added a condition to check if the sum of the 'Years since launch' and the 'Years Elapsed' is greater than or equal to the current year. If it is, the accumulated sales calculation will stop, preventing the curve from extending beyond the current year.

Make sure you have a column named 'Years Elapsed' in your 'Years since launch' table, which represents the number of years elapsed since the launch. If you don't have this column, you can create it using the following DAX formula:

Years Elapsed = YEAR(TODAY()) - 'Years since launch'[Years since launch]

This assumes that your 'Years since launch' table has a column named 'Years since launch' containing the launch years of the products. Adjust the column names accordingly based on your actual data model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[v-zhangti · Accepted solution]

Hi, @UCR522 

You can try the following methods.

Result =
VAR _Sum =
    CALCULATE ( SUM ( 'Sales'[Sales Amount] ),
        FILTER ( ALL ( 'Years since launch' ),
            'Years since launch'[Years since launch]
                <= MAX ( 'Years since launch'[Years since launch] )
        )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Years since launch'[Years since launch] )
            <= YEAR ( TODAY () )- CALCULATE ( MIN ( 'Years since launch'[Year] ), ALL ( 'Years since launch' ) ) + 1,
        _Sum,
        BLANK ()
    )

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[123abc · Accepted solution]

You can use this modified Dax:

Accumulated Sales =
CALCULATE(
SUM('Sales'[Sales Amount]),
FILTER(
ALL('Years since launch'),
'Years since launch'[Years since launch] <= MAX('Years since launch'[Years since launch]) &&
'Years since launch'[Years since launch] + [Years Elapsed] >= YEAR(TODAY())
)
)

In this formula, I've added a condition to check if the sum of the 'Years since launch' and the 'Years Elapsed' is greater than or equal to the current year. If it is, the accumulated sales calculation will stop, preventing the curve from extending beyond the current year.

Make sure you have a column named 'Years Elapsed' in your 'Years since launch' table, which represents the number of years elapsed since the launch. If you don't have this column, you can create it using the following DAX formula:

Years Elapsed = YEAR(TODAY()) - 'Years since launch'[Years since launch]

This assumes that your 'Years since launch' table has a column named 'Years since launch' containing the launch years of the products. Adjust the column names accordingly based on your actual data model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.