Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
UCR522
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

 

UCR522_0-1700582037324.png

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

2 ACCEPTED SOLUTIONS
123abc
Community Champion
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.

View solution in original post

v-zhangti
Community Support
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 ()
    )

vzhangti_0-1700727001590.png

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.

View solution in original post

2 REPLIES 2
v-zhangti
Community Support
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 ()
    )

vzhangti_0-1700727001590.png

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
Community Champion
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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.