Forum Discussion
Previous Row Cell multiplied with This Row Cell
Hi ilovasen ,
Try the following code:
DFPrev4 = VAR CurrentYear = 'Table'[Year] VAR Restart = 'Table'[EAver] - 'Table'[Year] VAR TableFIlter = FILTER(ALL('Table'), 'Table'[EAver] - 'Table'[Year] = Restart && 'Table'[Year] <= CurrentYear) RETURN PRODUCTX(TableFIlter , 'Table'[DFUnsec] )Has you can see the values are restarting at every 1996:
I just did a couple of lines for each but it works properly.
Basically what I'm doing is to chek the value for the dataset in this case 110000, 120000, 130000 and just filtered out the ones that start with that value.
In concern to the acceped questions you can accept has many has you want.
Super UserHi ilovasen ,
In my opinion you should move this to the Power Query and not on dax either way here are the codes for DAX and for M language:
M Language
try #"Renamed Columns"{[Year2]-1997}[DiscFactorUnsec] * [DiscFactorUnsec] otherwise [DiscFactorUnsec]
New Calculation = var MaxValue = MAXX(FILTER('Table', 'Table'[Year2] = EARLIER('Table'[Year2]) - 1), 'Table'[DiscFactorUnsec]) * 'Table'[DiscFactorUnsec]
REturn
IF(MaxValue = BLANK(),'Table'[DiscFactorUnsec], MaxValue)
Hi MFelix
Thanks a lot for a very quick response!
The problem of moving it to Power Query is that the rows are build on a Crossjoin of 2 tables within the Power BI (PBI) model, that are updated monthly. I would need to have the calculation taken place within the PBI solution using DAX (Measure or Calculated Column).
Your solution encounters the same problem that I am having (in my solution) that in the 3rd row, where the 5,301090693 is expected to be calulated with 0,9456 (the 3rd row of the the DiscFactorUnsec column) to give 5,0222 as in the 3rd row of my DFPrev4 column.
I could use both your and my formula to build one DFPrevX column(s) for all the years (105 * 2), but that is a hard and slow solution with this number of columns.
I could also import scenarios from Excel but would loose the interactivity of other users of the model.
- MFelix
Hi ilovasen ,
Now I understand what you want to achieve you just need to use the ProductX formula:
Column:
Column = PRODUCTX(FILTER('Table', 'Table'[Year2] <= EARLIER('Table'[Year2])), 'Table'[DiscFactorUnsec])Measure = PRODUCTX(FILTER(ALL('Table'[DiscFactorUnsec], 'Table'[Year2]), 'Table'[Year2] <= SELECTEDVALUE('Table'[Year2])), 'Table'[DiscFactorUnsec]) === This measure may need changes if you want it to be dynamic based on filters.My only question the last number what do you want to achieve is it the 0.7619 ir 4.471
Hi Olufemi7 & MFelix
Thanks for great contributions. I am really grateful for your help!
Both versions works well in the context it was originally presented. Didn't see Olufemi7 's version at once as it was threaded with an earlier response.
(Can I approve both as solutions or does on disappear if I accept it?)
I have also another challenge though;
This calculation relates to a longer table where
1. each calculation applies for 105 rows (years)
2. a Dataset-column (EAVer) running for all 386 datasets - each with 105 years - numbered from 11 for 105 years, 12 for 105 years to 3870 for 105 years
3. The "New Calculation"/"Column" formula should then have a new starting value in each 1996 (which is taken from the DiscFactorUnsec-column on the "current-row" and therefore not recursive)
4. Is there a simple amendment to the Formula that can facilitate this?
Continued table- MFelix
Hi ilovasen ,
Try the following code:
DFPrev4 = VAR CurrentYear = 'Table'[Year] VAR Restart = 'Table'[EAver] - 'Table'[Year] VAR TableFIlter = FILTER(ALL('Table'), 'Table'[EAver] - 'Table'[Year] = Restart && 'Table'[Year] <= CurrentYear) RETURN PRODUCTX(TableFIlter , 'Table'[DFUnsec] )Has you can see the values are restarting at every 1996:
I just did a couple of lines for each but it works properly.
Basically what I'm doing is to chek the value for the dataset in this case 110000, 120000, 130000 and just filtered out the ones that start with that value.
In concern to the acceped questions you can accept has many has you want.