Forum Discussion

AdamPBIDev's avatar
AdamPBIDev
Regular Visitor
4 years ago
Solved

Recursive Dax Calculation

Dear Dax Gurus,

 

Would appreciate some help - I'm looking to create a calculated column forecast which at index 0 simply takes the (Activity + (Activity * Activity Change %)), but for all other indexes takes the previous row calculated forecast and applies the current row activity change %. 

Note the index resets at each change in (Cost Centre)"Code".

 

Screenshot 1

 

Sample Data

 

Month YearCodeActivity Change %ActivityIndexForecast Formula
01/03/20221002.0%304803108 =D2+(D2*C2)
01/04/2022100-4.0% 12984 =F2+(F2*C3)
01/05/20221005.0% 23134  
01/06/20221007.0% 33353  
01/07/2022100-4.0% 43219  
01/08/20221008.0% 53476  
01/09/2022100-3.0% 63371  
01/10/20221002.0% 73439  
01/11/20221004.0% 83576  
01/12/2022100-6.0% 93363  
01/03/20222001.7%344803506 =D12+(D12*C12)
01/04/2022200-3.4% 13387 =F12+(F12*C13)
01/05/20222004.2% 23530  
01/06/20222005.9% 33739  
01/07/2022200-3.4% 43611  
01/08/20222006.8% 53857  
01/09/2022200-2.6% 63757  
01/10/20222001.7% 73822  
01/11/20222003.4% 83952  
01/12/2022200-5.2% 93749  

 

I did try Gerhard Brueckl's interesting approach for creating this entirely in DAX Link  but unfortunately I couldn't get this to work at Cost Centre Code level (works perfectly at an overall level) so have gone down the path of creating the above table instead

 

Thanks in advance

Adam

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    AdamPBIDev , Try a new column,

     

    Value = MAXX(filter(Data, [Index] =0 && [Code] =EARLIER(Data[Code])),[Activity])* PRODUCTX(Filter(DATA, [Code] =EARLIER(Data[Code]) && [Index]  <=EARLIER(Data[Index])), 1+[Activity Change %])

     

    File attached after signature

     

     

    • regnig's avatar
      regnig
      Frequent Visitor
      1 year ago

      Hi, amitchandak 

      I'm facing similar problem with recursive.

      lets say i have an additional column called Cons. Also the formula is described bellow.

      F2=C2*(D2 + Cons)

      F3=D2*(F2 + Cons)

      I've been struggling for that formula. would you like to give some advice. Thank you

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    1 year ago

    AdamPBIDev regnig This should be done in PQ as List.Accumulate allows for easy recursion.

     

    let
    Source = Table,
    Group = Table.Group (
        Source,
        { "Code" },
        {
            {
                "All",
                each
                    let
                        Source = _,
                        SortByIndex = Table.Sort ( Source, { { "Index", Order.Ascending } } ),
                        RemovedOtherColumns = Table.SelectColumns (
                            SortByIndex,
                            { "Activity Change %", "Activity" }
                        ),
                        ToRows = Table.ToRows ( RemovedOtherColumns ),
                        Transform = List.Accumulate (
                            ToRows,
                            {},
                            ( s, c ) =>
                                let
                                    a = if c{1} = null then List.Last ( s ) else c{1},
                                    b = s & { a + ( c{0} * a ) }
                                in
                                    b
                        ),
                        JoinColumns = Table.FromColumns (
                            Table.ToColumns ( Source ) & { Transform },
                            Table.ColumnNames ( Source ) & { "Forecast" }
                        )
                    in
                        JoinColumns
            }
        }
    )[All],
    Combine = Table.Combine ( Group ),
    ChangedType = Table.TransformColumnTypes ( Combine, { { "Forecast", Currency.Type } } )
in
    ChangedType

     

    PBIX is attached below.

    • regnig's avatar
      regnig
      Frequent Visitor
      1 year ago

      Can we used DAX instead of PQ? I use DAX table to build the data table which mean I cannot use PQ. here my lates Calculated Column.

       

      Value2 =

      Var init =
      MAXX(
          filter(
              Sheet1,
              Sheet1[Date] = DATE(2025,1,2)
          ),
          [Activity] + Sheet1[Value]
      )

      Var o =
      PRODUCTX(
          Filter(
              Sheet1,
              Sheet1[Date]  <= EARLIER(Sheet1[Date])
          ),
          (IF(
              Sheet1[Date] = MINX(Sheet1, Sheet1[Date]),
              init,
              1 + Sheet1[Value]
          ) * Sheet1[Percetage])
      )

      return o
       
      but the result return (F2+F2*[Value])*[Percentage]. I expecting to get (F2+[Value])*[Percentage] actualy.

       

      Thank you.
      • AntrikshSharma's avatar
        AntrikshSharma
        Community Champion
        1 year ago

        regnig no, while iteration, temporarily computed values of the previous rows aren't available in DAX and the calculation depends on the previous row during current row. You can import the table create in DAX by using PQ and then use the M code.