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 in...
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.