cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

How to stop accumulated measure

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?

Javier

2 ACCEPTED SOLUTIONS
Community Champion

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.

Community Support

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.

2 REPLIES 2
Community Support

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.

Community Champion

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.

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors