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

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

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

 Date Factor Cumulative Factor 1/24/2024 1 1 1/25/2024 1 1 1/26/2024 1 1 1/29/2024 1 1 1/30/2024 1 1 1/31/2024 1 1 2/1/2024 1 1 2/2/2024 1 1 2/5/2024 1.011523688 1.011523688 2/6/2024 1 1.011523688 2/7/2024 1 1.011523688 2/8/2024 1 1.011523688 2/9/2024 1 1.011523688 2/12/2024 1.011764706 1.023423966 2/13/2024 1 1.023423966 2/14/2024 1 1.023423966 2/15/2024 1 1.023423966 2/16/2024 1.011780105 1.035480008 2/19/2024 1 1.035480008 2/20/2024 1 1.035480008 2/21/2024 1 1.035480008 2/22/2024 1 1.035480008 2/23/2024 1 1.035480008 2/26/2024 1 1.035480008 2/27/2024 1 1.035480008 2/28/2024 1 1.035480008 2/29/2024 1 1.035480008 3/1/2024 1 1.035480008 3/4/2024 1 1.035480008 3/5/2024 1 1.035480008 3/6/2024 1 1.035480008 3/7/2024 1.010526316 1.046379797 3/8/2024 1.00591716 1.052571394 3/11/2024 1 1.052571394 3/12/2024 1 1.052571394 3/13/2024 1 1.052571394 3/14/2024 1 1.052571394 3/15/2024 1 1.052571394 3/18/2024 1 1.052571394 3/19/2024 1 1.052571394 3/20/2024 1 1.052571394 3/21/2024 1 1.052571394 3/22/2024 1.00723589 1.060187685 3/25/2024 1 1.060187685 3/26/2024 1 1.060187685 3/27/2024 1 1.060187685 3/28/2024 1 1.060187685 4/2/2024 1 1.060187685 4/3/2024 1 1.060187685 4/4/2024 1 1.060187685
1 ACCEPTED SOLUTION
Super User

In this scenario a simple PRODUCTX does the trick

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

5 REPLIES 5
Super User

Hi,

This calculated column formula works

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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

Thank you and best regards

Zoran

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

In this scenario a simple PRODUCTX does the trick

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

Frequent Visitor

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

Announcements

Fabric certifications survey

Certification feedback opportunity for the community.

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors