Cumulative Calculation Multiplying Values from Previous Row

[jas_power]

Hello,

    I need to create a cumulative measure that references the previous rows values like this:

Where refPeriod comes from the dim table Periods, allItemsByItem comes from the fact table I_data and value comes from another fact table W_data.

The relationships between tables are as follows:

Periods[refPeriod] = I_data[refPeriod]  1 to many single direction 

Periods[basketYear] = W_data[basketYear]    many to many bi-directional

There is no relationship between I_data and W_data

Slicers for product, geography, start refPeriod and end refPeriod are used to bring the results into a table visual. 

I can identify the first selected reference period with if(min('Periods'[refPeriod]) = selStartDate, value..... where selStartDate is a measure that captures the value from one of my date slicers. The problem is how to figure out the calculation after the first row. 

First row logic: = value from the same row.

All other row logic: = the result of the measure in the previous row multiplied by the allItemsByItem in the previous row.

Any suggestions would be greatly appreciated. And I can provide more information if it's needed. 

[v-yueyunzh-msft · Accepted solution]

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

[jas_power]

Hi @v-yueyunzh-msft Thank you so much for taking the time to help me solve this problem. I have spent the day trying to different ways to tweak the suggested measure, but unfortunately, I can't figure out how to make it work with the actual data. 

When I try this updated measure:

revisedWeightV9 = 
var _min_date = MINX(ALLSELECTED('basket_effective_period'),[refPeriod])
var _value =MAXX( FILTER( ALLSELECTED('W_data'),'W_data'[refPeriod] =_min_date) , [value])
var _date = MAX('basket_effective_period'[refPeriod])
var _alltem =SELECTCOLUMNS( FILTER(ALLSELECTED('I_data') , 'I_data'[refPer] < _date) ,"alltem", [allItemsbyItem])
return
IF(_value* PRODUCTX(_alltem,[alltem])=BLANK(),_value,_value* PRODUCTX(_alltem,[alltem]))

The results would seem to be using the W_data[value] after the first row. I.E. 5.16 on the second row and beyond is being used in the calculation, but I need to only use the W_data[value] from the first row only and build the calculation from there. See results under column revisedWeightV9:

When I hardcode the _value variable in the measure I get the results I want, except the results seem to be offset by 1 meaning the result for 2007-07 should be the result on 2007-06 etc. I am not sure what's going on. You can refer to the column weightRevisedV7 above.

Here is the measure with the hardcoded value:

weightRevisedV7 = 
var _min_date = MINX(ALLSELECTED('basket_effective_period'),[refPeriod])
var _value = 5.58
var _date = MAX('basket_effective_period'[refPeriod])
var _alltem =SELECTCOLUMNS( FILTER(ALLSELECTED('I_data') , 'I_data'[refPer] < _date) ,"alltem", [allItemsbyItem])
return
IF(_value* PRODUCTX(_alltem,[alltem])=BLANK(),_value,_value* PRODUCTX(_alltem,[alltem]))

If I may request your assistance further:

1) How can I adjust the measure so the _value is dynamic but is always equal to the first selected W_data[value]?

2) Why do you think the results seem to be offset?

Not sure if it's worth mentioning, but I updated my data and model so that there is a one-to-many single directional relationship betweeen W_data and my date dimension table in case that was causing the issue.

Thanks in advance for any other guidance you can provide.

[v-yueyunzh-msft]

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

[jas_power]

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!