Forum Discussion
Cumulative Value Multiplication
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!
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,
4 Replies
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!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!
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,
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]
)
)
)
