Forum Discussion

ilovasen's avatar
ilovasen
Frequent Visitor
9 months ago
Solved

Previous Row Cell multiplied with This Row Cell

This is my first post in this Community.
I have been struggling for a week on what is a basic calculation in Excel, but in PowerBI/DAX a different animal.
I am trying to...
1. reference the previous cell (1 in DiscFactorUnsec) below and multiply with (2) to get (3) and repeat that for each row i.e. by

2. then use (3) to multiply with third row in DiscFactorUnsec (0,9456) to get (4 (in DFPrev4)) and so on as shown in the first 4 rows of DFPrev4 (correct up to (5 - Row 4) - Row 6 is wrong)

I think the problem might be to make an intermediate calculation to hold the previous product and multiply with present row's DiscFactorUnsec value and then again hold that value until the next row's calculation and so on. 

Any suggestion for a solution would be greatly appreciated.

 

CircularReference
DAX
  • MFelix's avatar
    MFelix
    9 months ago

    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.

     



8 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    9 months ago

    Hi 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)

     

     

    • ilovasen's avatar
      ilovasen
      Frequent Visitor
      9 months ago

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        9 months ago

        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

         

         

  • Olufemi7's avatar
    Olufemi7
    Icon for Super User rankSuper User
    9 months ago

    Hi ilovasen, 

    You’re looking for a way to replicate the “previous row × current row” logic from Excel in Power BI/DAX. The easiest way to do this is to create a calculated column that performs a cumulative product across rows.

    If your table is ordered by Year2 (or another index column), you can use:

    DFPrev4 =
VAR CurrentYear = 'Table'[Year2]
RETURN
CALCULATE(
    PRODUCT('Table'[DiscFactorUnsec]),
    FILTER(
        'Table',
        'Table'[Year2] <= CurrentYear
    )
)

     

    Explanation

    • VAR CurrentYear stores the current row’s year.

    • FILTER(... <= CurrentYear) selects all rows up to the current one.

    • PRODUCT(...) multiplies all DiscFactorUnsec values in that set, giving you the cumulative multiplication you want.

     If Year2 isn’t strictly sequential or unique, add an Index column in Power Query and replace Year2 with that index in the formula:

    DFPrev4 =
VAR CurrentIndex = 'Table'[RowIndex]
RETURN
CALCULATE(
    PRODUCT('Table'[DiscFactorUnsec]),
    FILTER(
        'Table',
        'Table'[RowIndex] <= CurrentIndex
    )
)

     

    This way, each row’s result is the product of all previous rows plus the current one exactly.