Cumulative Value Multiplication

[USEERTEST51151]

Hello everyone,

I’m encountering an issue with a cumulative measure I’m working on, and I’d appreciate some guidance. The desired output format looks like this:

Year Measure1 Cumulative

2019	1.08	1
2020	1.05	1.05
2021	1.45	1.5225
2022	1.68	2.5578
2023	1	2.5578
2024	1	2.5578

For the year 2019, the cumulative value is always 1. For each subsequent year, it’s calculated by multiplying the previous year’s cumulative value with the current year’s Measure1 value. Here’s how it’s calculated for each year:

• 2020 cumulative = 1 (2019 cumulative) * 1.05 (Measure1 for 2020) = 1.05
• 2021 cumulative = 1.05 (2020 cumulative) * 1.45 (Measure1 for 2021) = 1.5225
• 2022 cumulative = 1.5225 (2021 cumulative) * 1.68 (Measure1 for 2022) = 2.5578
• 2023 cumulative = 2.5578 (2022 cumulative) * 1 (Measure1 for 2023) = 2.5578
• 2024 cumulative = 2.5578 (2023 cumulative) * 1 (Measure1 for 2024) = 2.5578

Any insights on how to implement of the Cumulative measure or troubleshoot this would be greatly appreciated!

[DataNinja777 · Accepted solution]

Hi @USEERTEST51151 ,

Building on the measure provided by @saud968 , I've tweaked it to introduce the base year 2019 to use 1 instead of 1.08 as shown below:

Cumulative Measure = 
VAR CurrentYear = MAX('Table'[Year])
RETURN
    CALCULATE(
        PRODUCTX(
            FILTER(
                ALL('Table'),
                'Table'[Year] <= CurrentYear
            ),
            IF('Table'[Year] = 2019, 1, 'Table'[Measure1])  // Use 1 for 2019, Measure1 for other years
        )
    )

The resulting output is as shown below:

I've attached an example pbix file for your reference.

Best regards,

[saud968]

Try this measure 

Cumulative Measure =
VAR CurrentYear = MAX('Table'[Year])
RETURN
CALCULATE(
PRODUCTX(
FILTER(
ALL('Table'),
'Table'[Year] <= CurrentYear
),
'Table'[Measure1]
)
)

If this does not work share some more details. 

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[saud968]

@USEERTEST51151 was the above helpful

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[anmolmalviya05]

Hi @USEERTEST51151, Please try the below measures.

Cumulative Measure =
VAR CurrentYear = MAX('Table'[Year])
RETURN
IF(
CurrentYear = 2019, -- Base case for 2019
1,
CALCULATE(
PRODUCTX(
FILTER(
'Table',
'Table'[Year] <= CurrentYear -- Include all rows up to the current year
),
'Table'[Measure1]
)
)
)