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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ARob198
Helper IV
Helper IV

Cumulative Multiplication measure

Hello,

 

I am trying to create a dynamic measure that multiplies the value for all dates before today.  I do not know how to do this in Power BI.  Can anyone help?

 

For example- I am looking to replicate the values after calculation.  These are just the cumulative multiplied Value 1 for each day:

DayValue 1Value After calculation
Mon1.11.1
Tues1.151.265
Weds1.121.4168
Thurs1.161.643488
Friday1.091.79140192

 

 

1 ACCEPTED SOLUTION

@ARob198 not sure what you are referring to purple, share screenshot, here is how you can add 2nd filter

 

Measure = 
VAR __d = MAX ( 'Day'[Index] ) 
RETURN CALCULATE ( PRODUCTX ( 'Day', [Value 1] ), 
ALLSELECTED ( 'Day' ), 'Day'[Index] <= __d, 'Date'[Date] > DATE(2016,1,1) )

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

10 REPLIES 10
parry2k
Super User
Super User

@Anonymous sorry not fully clear what you are looking for. If you can provide a sample pbix with the expected output, it will help to provide the solution. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

TomMartens
Super User
Super User

Hey @ARob198 ,

 

this is not as simple as we may wish 🙂

Fortunately, it possible to achieve this by leveraging the table iterator function PRODUCTX.

 

As PRODUCTX expects a table as the first parameter, you have to order the Day e.g., by creating a new column like DayIndex with 1 for Monday, 2 for Tuesday, ...

Then you can create a measure similar to this:

measure = 
PRODUCTX(
FILTER(
'tablename'
,'tablename'[DayIndex] <= MAX('tablename'[DayIndex]) 
)
,'tablename'[Value]
)

This blog post describes what's going on by using PRODUCTX in much more detail.

 

Hopefully, this provides what you are looking for.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi All,

 

I have a Date in the model so I believe that should work instead of an index column.  However, how to I add a filter to have it start caclulating on a certain date.  Both of the ways you suggested are resulting in a 0 value which I suspect might be becuase the very first value is a 0.  Therefore, multiplying anything by 0 = 0.  I tried adding a second filter 'Values[DATE] > "12/31/2016" and also Values[DATE] > 42735 but it doesn't seem to work.  How can I add a second filter?

 

On another note, why do some components of measures in Power BI turn purple?  As in the formula bar text becomes purple?  What does that mean?

 

Thank you very much

@ARob198 not sure what you are referring to purple, share screenshot, here is how you can add 2nd filter

 

Measure = 
VAR __d = MAX ( 'Day'[Index] ) 
RETURN CALCULATE ( PRODUCTX ( 'Day', [Value 1] ), 
ALLSELECTED ( 'Day' ), 'Day'[Index] <= __d, 'Date'[Date] > DATE(2016,1,1) )

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

The formula is giving me 0s.  Even though my data has values for the columns to be multiplied- as in the post above.  The MTD Int is the value that I need to use for the cumulative multiplication.  Any thoughts on how to get this to work or even to trouble shoot this?  Thank you

@ARob198 Hello, did you solve this problem?

At this point I get into the same problem that you pose I want to do a cumulative multiplication but all the values appear to me in 0.

I'd appreciate it if you help me, thanks!

Thank you.  Here is a screenshot of the purple text and my formula.  It doesn't seem to be working for me, I am not sure why.  The MTD INT calculation is working so I just need it the ITD to be a cumulative multiplied table on this number.  What the best way to trouble shoot this?

 

table.pngformula.PNG

 

 

 
 

@ARob198 that shows it is a measure.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@ARob198 add an index column in power query as it is required to set the order for the day, after index column is added, apply the changes,

 

select day column and sort it by index and add the following measure

 

Measure = 
VAR __d = MAX ( 'Day'[Index] ) 
RETURN CALCULATE ( PRODUCTX ( 'Day', [Value 1] ), ALLSELECTED ( 'Day' ), 'Day'[Index] <= __d )

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi, I had a similar problem. I am creating this recurring product, But encountering issue where the memory is full as its about 38k rows. Is there any way to come around this issue for a similar logical outcome?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.