Forum Discussion
Cumulative Calculation Multiplying Values from Previous Row
Hi, jas_power
According to your description, you want to calculate the " cumulative measure that references the previous rows".
For your need , in your image you have many tables and make relationships between tables.
I don't have your raw data, and I can't simulate your corresponding inter-table relationships in my tests, but we use your sample data to simulate the logic of calculating this situation, and you can try to find the corresponding values in your inter-table relationships, and then replace the variables to achieve your needs, this is my test data:
We can create a measure like this:
Measure = var _min_date = MINX(ALLSELECTED('Table'),[refPeriod]) var _value =MAXX( FILTER( ALLSELECTED('Table'),'Table'[refPeriod] =_min_date) , [value]) var _date = MAX('Table'[refPeriod]) var _alltem =SELECTCOLUMNS( FILTER(ALLSELECTED('Table') , 'Table'[refPeriod] < _date) ,"alltem" , [alltemsbyItem]) return IF(_value* PRODUCTX(_alltem,[alltem])=BLANK(),_value,_value* PRODUCTX(_alltem,[alltem]))Then we put this measure in the visual and we will meet your need , the result is as follows:
If this can not helo you meet your need , can you share the .pbix file without sensitive data to us so that we can help you better!
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi, jas_power
Thank for your quick response and your trying.
According to your description, you use the [revisedWeightV9] measure , it returns the wrong value.I checked the screenshot and i think the reason is the “_value” parameter is not right.It seems it returns the current [value] in this row.
First, Here are the answers for your questions:
Q1:How can I adjust the measure so the _value is dynamic but is always equal to the first selected W_data[value]?
For this , i think the first is to resolve this question.
Now, in the measure, we use this dax code to get the value:
var _min_date = MINX(ALLSELECTED('basket_effective_period'),[refPeriod])
var _value =MAXX( FILTER( ALLSELECTED('W_data'),'W_data'[refPeriod] =_min_date) , [value])
In this logic, the “_min_date” is to find the minimum refPeriod in the ‘basket_effective_period’ table. Then we use this minimum refPeriod to get the 'W_data'[value] . Ideally, this should always return a value (5.58).
But for this , it seems not work. I think you need to determine whether getting the value of the first row between the' basket_effective_period' table and the' W_data' table is related by the [refPeriod] field? If not, you need to modify the _min_date and _value variables. You need to modify the judgment logic that you want to get _value. For this kind of test, you can create a measure value, test the value returned by this _value separately, until you show the desired _value=5.58 in each line, and then replace this variable.
Q2:Why do you think the results seem to be offset?
For this question, I don't quite understand what you mean. The display of weightRevisedV7 in screenshot seems to be correct. For example, the value of July 2007 = 5.58 * 1.00551 ≈ 5.61.
Finally, thank you very much for modifying the model, which relates to one-to-many relationship. We strongly recommend using many-to-many relationships, because there will be complex model processing. Secondly, if this still can't help your needs, can you provide us with the test data of three tables, the relationship between the three tables (in what fields), and the output results you want in the form of tables or. pbix files?
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi v-yueyunzh-msft Aniya! I can't thank you enough for your help on this problem. I doubt I ever would have solved this on my own. I will mark you original post as the solution because if I had a more straight-forward model without all these disconnected tables for slicers, then it would have worked. Because I did have some disconnected slicers, I needed to adjust the _min_date variable so that it was filtered by the slicers and I had to add a test condition to _value to remove these hidden duplicated rows. Once I implemented those two things, your measure worked like a charm. Here is the updated measure for reference:
weightRevisedV9 =
var selProducts = ALLSELECTED(prod_map[W_prodId])
var selGeo = SELECTEDVALUE(geoMap[W_geoId])
var selStartDate = SELECTEDVALUE(startDate[refPeriod])
var selEndDate = SELECTEDVALUE(endDate[refPeriod])
var _min_date = CALCULATE(MINX(ALLSELECTED('W_data'),[refPeriod]),FILTER(ALL('W_data'),W_data[prodId] in selProducts && W_data[geoId] = selGeo && W_data[refPeriod] >= [selStartDate] && W_data[refPeriod] <= [selEndDate]))
var test = CALCULATE ( SUM ( W_data[value] ), VALUES ( basket_effective_period[refPeriod] ) )
var _value =if(test <> BLANK(),MAXX( FILTER( ALLSELECTED('W_data'),'W_data'[refPeriod] =_min_date) , [value]))
var _date = MAX('basket_effective_period'[refPeriod])
var _allitem =SELECTCOLUMNS( FILTER(ALLSELECTED('I_data') , 'I_data'[refPer] < _date) ,"allitem", [allItemsbyItem])
return
IF(_value* PRODUCTX(_alltem,[allitem])=BLANK(),_value,_value* PRODUCTX(_allitem,[allitem]))
Thank you again. You are my hero!