Forum Discussion
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:
| Day | Value 1 | Value After calculation |
| Mon | 1.1 | 1.1 |
| Tues | 1.15 | 1.265 |
| Weds | 1.12 | 1.4168 |
| Thurs | 1.16 | 1.643488 |
| Friday | 1.09 | 1.79140192 |
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.⚡
10 Replies
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.⚡
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
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.⚡
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.
