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
Zoran05
Frequent Visitor

Please help: Cumulative totals returns in a calculated column

Dear Community, 

 

I'm trying to transfer some excel reports into Power BI reports. I have the below data with a column Date and Factor. Usually the Factor is 1, which means there are no changes in a Factor. From time to time the Factor can increase by a certain value (e.g. 1.01152...). I'm trying to create a calculated column in Power BI for the "Cumulative Factor" like I have in Excel. 

 

In Excel this is very easy as shown in the screenshot. It's multiplying the previous cumulative value with the current Factor. 

For example, the calculation on the 2/12/2024 is the following: 1.01152 * 1.01176 = 1.02342

Zoran05_0-1713533338221.png

 

Please help me to create the calcutated column in Power BI. Any help or suggestion is well appriciated. 

 

Thank you very much and best regards

Zoran

 

 

DateFactorCumulative Factor
1/24/202411
1/25/202411
1/26/202411
1/29/202411
1/30/202411
1/31/202411
2/1/202411
2/2/202411
2/5/20241.0115236881.011523688
2/6/202411.011523688
2/7/202411.011523688
2/8/202411.011523688
2/9/202411.011523688
2/12/20241.0117647061.023423966
2/13/202411.023423966
2/14/202411.023423966
2/15/202411.023423966
2/16/20241.0117801051.035480008
2/19/202411.035480008
2/20/202411.035480008
2/21/202411.035480008
2/22/202411.035480008
2/23/202411.035480008
2/26/202411.035480008
2/27/202411.035480008
2/28/202411.035480008
2/29/202411.035480008
3/1/202411.035480008
3/4/202411.035480008
3/5/202411.035480008
3/6/202411.035480008
3/7/20241.0105263161.046379797
3/8/20241.005917161.052571394
3/11/202411.052571394
3/12/202411.052571394
3/13/202411.052571394
3/14/202411.052571394
3/15/202411.052571394
3/18/202411.052571394
3/19/202411.052571394
3/20/202411.052571394
3/21/202411.052571394
3/22/20241.007235891.060187685
3/25/202411.060187685
3/26/202411.060187685
3/27/202411.060187685
3/28/202411.060187685
4/2/202411.060187685
4/3/202411.060187685
4/4/202411.060187685
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

In this scenario a simple PRODUCTX does the trick

 

Cumulative = PRODUCTX(filter(all('Table'),[Date]<=max('Table'[Date])),[Factor])

 

lbendlin_0-1713651016230.png

 

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

This calculated column formula works

Column = CALCULATE(PRODUCT(Data[Factor]),FILTER(Data,Data[Date]<=EARLIER(Data[Date])))

Hope this helps.

Ashish_Mathur_0-1713658519929.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur ,

 

Thank you so much for your quick reply. Works exactly as epected. 

 

Thank you and best regards

Zoran

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

In this scenario a simple PRODUCTX does the trick

 

Cumulative = PRODUCTX(filter(all('Table'),[Date]<=max('Table'[Date])),[Factor])

 

lbendlin_0-1713651016230.png

 

Hi @lbendlin , 

 

Thank you so much for your quick reply. It's indeed a simple solution and works very fast. 

 

Thank you and best regards

Zoran

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.