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
Icon for Community Champion rankCommunity 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.